如何在从另一个表中提取数据时创建具有约束的表 [英] How do I create a table with constraints while pulling data from another table
问题描述
我正在尝试创建一个writers表,其中包含作者ID,姓氏,名字和ISBN以及每位作者撰写的书名.使用与author和books表相同的数据类型,但不从这些表中复制数据.并且在作者表中包括作者ID作为主键,标题不为空,并且将ISBN作为引用ISBN的外键.但是,我收到违反了唯一约束(WT_PK)"错误:
I am trying to create a writers table that contains the author ID, last name, first name, and ISBN and title of the book each author wrote. While using the same data types as the author and books table, but not copying the data from these tables. And include the author ID as the primary key, the title as not null, and the ISBN as the foreign key referencing the ISBN in the books table. However I receive the "unique constraint (WT_PK) violated" error:
CREATE TABLE writers
(authorid VARCHAR2(4),
lname VARCHAR2(10),
fname VARCHAR2(10),
isbn VARCHAR2(10),
title VARCHAR2(30) CONSTRAINT title_nn NOT NULL,
CONSTRAINT wt_pk PRIMARY KEY(authorid),
CONSTRAINT wt_fk FOREIGN KEY(isbn) REFERENCES books(isbn));
INSERT INTO writers
SELECT authorid, fname, lname, isbn, title
FROM author
JOIN bookauthor USING(authorid)
JOIN books USING(isbn);
推荐答案
似乎您需要为WRITERS表使用复合键.例子 (已在Oracle 12c和11g上测试,dbfiddle 此处):
It seems that you need to use a composite key for your WRITERS table. Example (tested with Oracle 12c and 11g, dbfiddle here):
-- 4 authors
create table author ( authorid primary key, fname, lname )
as
select 1, 'fname_1', 'lname_1' from dual union all
select 2, 'fname_2', 'lname_2' from dual union all
select 3, 'fname_3', 'lname_3' from dual union all
select 4, 'fname_4', 'lname_4' from dual ;
-- 7 books
create table books ( isbn primary key, title )
as
select '978-1449324451', 'title_1' from dual union all
select '978-1449324452', 'title_2' from dual union all
select '978-1449324453', 'title_3' from dual union all
select '978-1449324454', 'title_1_4' from dual union all
select '978-1449324455', 'title_2_4' from dual union all
select '978-1449324456', 'title_3_4' from dual union all
select '978-1449324457', 'title_4_4' from dual ;
-- suppose that 4 books are written by one and the same author
create table bookauthor( authorid, isbn )
as
select A.authorid, B.isbn
from author A
join books B on A.authorid = substr( B.title, length( B.title ), 1 ) ;
向BOOKAUTHOR表添加一些约束,并检查其内容:
Add some constraints to the BOOKAUTHOR table, and check its contents:
-- authorid, isbn
alter table bookauthor
add (
constraint ba_fk1 foreign key( authorid ) references author( authorid )
, constraint ba_fk2 foreign key( isbn ) references books( isbn )
, constraint ba_pk primary key ( authorid, isbn )
) ;
SQL> select * from bookauthor;
AUTHORID ISBN
---------- --------------
1 978-1449324451
2 978-1449324452
3 978-1449324453
4 978-1449324454
4 978-1449324455
4 978-1449324456
4 978-1449324457
原始" DDL代码(稍作修改)->插入失败
"Original" DDL code (with minor modifications) -> INSERT fails
create table writers (
authorid varchar2( 4 )
, lname varchar2( 10 )
, fname varchar2( 10 )
, isbn char( 14 )
, title varchar2( 30 ) constraint title_nn not null
, constraint wt_pk primary key ( authorid )
, constraint wt_fk foreign key( isbn ) references books( isbn )
);
INSERT INTO writers
SELECT authorid, fname, lname, isbn, title
FROM author
JOIN bookauthor USING(authorid)
JOIN books USING(isbn);
-- ORA-00001: unique constraint (...WT_PK) violated
-- author 4 with 4 books!
建议的DDL代码(和测试):
Suggested DDL code (and testing):
create table writers2 (
authorid varchar2( 4 )
, lname varchar2( 10 )
, fname varchar2( 10 )
, isbn char( 14 )
, title varchar2( 30 ) constraint title_nn2 not null
, constraint wt_pk2 primary key ( authorid, isbn )
, constraint wt_fk2 foreign key( isbn ) references books( isbn )
);
INSERT INTO writers2
SELECT authorid, fname, lname, isbn, title
FROM author
JOIN bookauthor USING(authorid)
JOIN books USING(isbn);
-- 7 rows inserted.
从WRITERS2中选择
SELECTing from WRITERS2:
SQL> select * from writers2 ;
AUTH LNAME FNAME ISBN TITLE
---- ---------- ---------- -------------- ------------------------------
1 fname_1 lname_1 978-1449324451 title_1
2 fname_2 lname_2 978-1449324452 title_2
3 fname_3 lname_3 978-1449324453 title_3
4 fname_4 lname_4 978-1449324454 title_1_4
4 fname_4 lname_4 978-1449324455 title_2_4
4 fname_4 lname_4 978-1449324456 title_3_4
4 fname_4 lname_4 978-1449324457 title_4_4
不过,不确定为什么需要WRITERS表-因为您可以通过运行查询来生成其数据.
Not sure why you would need the WRITERS table, though - as you can generate its data by running a query.
这篇关于如何在从另一个表中提取数据时创建具有约束的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!