SQL ALTER TABLE 删除级联

Question

我有下表:

CREATE TABLE BOOK_AUTHORS
(Book_id CHAR(20) NOT NULL,
AuthorName VARCHAR(30) NOT NULL,
PRIMARY KEY (Book_id, AuthorName),
FOREIGN KEY (Book_id) REFERENCES BOOK (Book_id));

CREATE TABLE BOOK_COPIES
(Book_id CHAR(20) NOT NULL,
Branch_id CHAR(20) NOT NULL,
No_of_copies NUMBER,
PRIMARY KEY (Book_id, Branch_id),
FOREIGN KEY (Book_id) REFERENCES BOOK (Book_id),
FOREIGN KEY (Branch_id) REFERENCES LIBRARY_BRANCH (Branch_id));

我想向它们添加ON DELETE CASCADE 约束:

我第一次尝试时说它有效。该文件如下所示:

ALTER TABLE "BOOK_AUTHORS"
ADD CONSTRAINT "fk_test"
FOREIGN KEY ("Book_id")
REFERENCES "BOOK" ("Book_id")
ON DELETE CASCADE;

然后我为第二个表中的两个外键创建了两个单独的表:

ALTER TABLE "BOOK_COPIES"
ADD CONSTRAINT "fk_test1"
FOREIGN KEY ("Book_id")
REFERENCES "BOOK" ("Book_id")
ON DELETE CASCADE;

ALTER TABLE "BOOK_COPIES"
ADD CONSTRAINT "fk_test2"
FOREIGN KEY ("Branch_id")
REFERENCES "LIBRARY_BRANCH" ("Branch_id")
ON DELETE CASCADE;

但是，这样做后我收到了错误

"Book_id" invalid identifier

然后

"Branch_id" invalid identifier

我不知道我做错了什么。然后我返回并再次执行第一个更改表(我最初认为有效的那个)，它给了我相同的错误消息(“Book_id”无效标识符)。有人可以帮我添加这些限制吗？我还有其他五个表要添加这些约束。

Answer 1

如果您在标识符两边加上双引号(就像您在

中所做的那样)

ALTER TABLE "BOOK_COPIES"
ADD CONSTRAINT "fk_test1"
FOREIGN KEY ("Book_id")
REFERENCES "BOOK" ("Book_id")
ON DELETE CASCADE;

)您的标识符(例如本例中的“Book_id”)区分大小写。

因此，您必须更改表定义并将列重命名为“Book_id”，或者(最好是恕我直言)删除约束定义中的双引号:

ALTER TABLE BOOK_COPIES
ADD CONSTRAINT fk_test1
FOREIGN KEY (Book_id)
REFERENCES BOOK (Book_id)
ON DELETE CASCADE;