下面我创建了一个包含主键、外键、非空约束的表,但我不断收到错误
"%s: 标识符无效">
创建表编写器 ((作者编号VARCHAR2(4(, 姓氏 VARCHAR2(10(, 名字 VARCHAR2(10(, 国际标准书号VARCHAR2(10(, 标题 VARCHAR2(30((,
(约束wt_pk主键 (作者ID(, 约束wt_nn不为空(标题(, 约束wt_fk外键 (ISBN( 参考书 (ISBN(((;
首先创建表,然后尝试插入到选择中。
CREATE TABLE writers
(
authorid <datatype>,
last name <datatype>,
first name <datatype>,
isbn <datatype>,
title <datatype>,
CONSTRAINT wt_pk PRIMARY KEY (authorid),
CONSTRAINT wt_nn NOT NULL (title),
CONSTRAINT wt_fk FOREIGN KEY (isbn) REFERENCES books (isbn)
) ;
insert into writers (authorid, last name, first name, isbn, title)
SELECT authorid, fname, lname, isbn, title
FROM author
JOIN bookauthor USING (authorid)
JOIN books USING (isbn);
Oracle不允许你用as
指定表定义。 因此,请先创建表,然后添加约束:
CREATE TABLE writers as
SELECT authorid, fname, lname, isbn, title
FROM author JOIN
bookauthor
USING (authorid) JOIN
books
USING (isbn);
ALTER TABLE writers
ADD CONSTRAINT wt_pk PRIMARY KEY (authorid);
ALTER TABLE writers
ADD CONSTRAINT wt_nn NOT NULL (title);
ALTER TABLE writers
ADD CONSTRAINT wt_fk FOREIGN KEY (isbn) REFERENCES books (isbn);
据推测,您实际上希望主键与(authorid, isbn)
而不是仅authorid
.