SQL ALTER TABLE DE删除CASCADE [英] SQL ALTER TABLE ON DELETE CASCADE

问题描述

我有以下表格：

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 约束到他们两个：

I want to add ON DELETE CASCADE constraints to the both of them:

我第一次尝试它说它工作。该文件如下所示：

The first time I tried it said it worked. That file looks like:

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

然后我浏览了第二个表中的两个外键， p>

Then I went through and made two separate tables for the two foreign keys in the second table:

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;

但是，这样做时会出现错误

However, upon doing so I got the errors

Book_id无效的标识符

"Book_id" invalid identifier

，然后

Branch_id无效的标识符

"Branch_id" invalid identifier

我不知道我做错了什么。然后我回去，并再次做了第一个alter table（我原来认为工作），它给了我相同的错误消息（Book_id无效标识符）。有人可以帮我添加这些约束吗？我还有另外五个表来添加这些约束。

I don't know what I did wrong. I then went back and did the first alter table again (the one that I originally thought worked) and it gave me the same error message ("Book_id" invalid identifier). Can someone help me add these constraints? I also have five other tables to add these constraints to.

推荐答案

如果你在你的标识符

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

您的标识符（例如在这种情况下为Book_id）会区分大小写。

) your identifiers (e.g. "Book_id" in this case) become case-sensitive.

因此，您必须更改表定义并将列重命名为Book_id或者（最好是IMHO）只是去掉你的约束定义中的双引号：

So either you'll have to change your table definition and rename the column to "Book_id" or (much preferably IMHO) just get rid of the double quotes in your constraint definition:

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

这篇关于SQL ALTER TABLE DE删除CASCADE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！