SQL ON DELETE CASCADE，删除是通过什么方式发生的？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13444859/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
提问by Oliver Spryn
If I have two relations in a database, like this:
如果我在数据库中有两个关系，如下所示：
CREATE TABLE Courses (
CourseID int NOT NULL PRIMARY KEY,
Course VARCHAR(63) NOT NULL UNIQUE,
Code CHAR(4) NOT NULL UNIQUE
);
CREATE TABLE BookCourses (
EntryID int NOT NULL PRIMARY KEY,
BookID int NOT NULL,
Course CHAR(4) NOT NULL,
CourseNum CHAR(3) NOT NULL,
CourseSec CHAR(1) NOT NULL
);
and I establish a foreign key relationship between the two, like this:
我在两者之间建立了外键关系，如下所示：
ALTER TABLE BookCourses
ADD FOREIGN KEY (Course)
REFERENCES Courses(Code)
ON DELETE CASCADE;
Then you can see that the Courseattribute in the BookCoursesrelation references the Codeattribute in the Coursesrelation.
然后，你可以看到，Course在属性BookCourses关系引用Code属性的Courses关系。
My question is when a deletion occurs in either of the two relations, which way does the deletion cascade? If I delete a tuple in the Coursesrelation, will it delete all referencing tuples in the BookCoursesrelation, or is it the other way around?
我的问题是当两个关系中的任何一个发生删除时，删除以哪种方式级联？如果我删除Courses关系中的一个元组，它会删除关系中的所有引用元组BookCourses，还是相反？
回答by John Woo
Cascade will work when you delete something on table Courses. Any record on table BookCoursesthat has reference to table Courseswill be deleted automatically.
当您删除 table 上的某些内容时，Cascade 将起作用Courses。表BookCourses上任何引用表的记录Courses都将被自动删除。
But when you try to delete on table BookCoursesonly the table itself is affected and not on the Courses
但是当您尝试在表上删除时，BookCourses只有表本身受到影响，而不是在表上Courses
follow-up question: why do you have CourseIDon table Category?
后续问题：为什么你CourseID在桌子上有Category？
Maybe you should restructure your schema into this,
也许你应该将你的架构重组为这个，
CREATE TABLE Categories
(
Code CHAR(4) NOT NULL PRIMARY KEY,
CategoryName VARCHAR(63) NOT NULL UNIQUE
);
CREATE TABLE Courses
(
CourseID INT NOT NULL PRIMARY KEY,
BookID INT NOT NULL,
CatCode CHAR(4) NOT NULL,
CourseNum CHAR(3) NOT NULL,
CourseSec CHAR(1) NOT NULL,
);
ALTER TABLE Courses
ADD FOREIGN KEY (CatCode)
REFERENCES Categories(Code)
ON DELETE CASCADE;
回答by Morten Holmgaard
Here is a simple example for others visting this old post, but is confused by the example in the question:
这是其他人访问这篇旧帖子的简单示例，但对问题中的示例感到困惑：
Delivery -> Package (One -> Many)
交付 -> 包裹（一个 -> 多个）
CREATE TABLE Delivery(
Id INT IDENTITY PRIMARY KEY,
NoteNumber NVARCHAR(255) NOT NULL
)
CREATE TABLE Package(
Id INT IDENTITY PRIMARY KEY,
Status INT NOT NULL DEFAULT 0,
Delivery_Id INT NOT NULL,
CONSTRAINT FK_Package_Delivery_Id FOREIGN KEY (Delivery_Id) REFERENCES Delivery (Id) ON DELETE CASCADE
)
The entry with the foreign key Delivery_Id (Package) is deleted with the referenced entity in the FK relationship (Delivery).
外键Delivery_Id（Package）的条目与FK关系（Delivery）中的引用实体一起删除。
So when a Delivery is deleted the Packages referencing it will also be deleted. If a Package is deleted nothing happens to any deliveries.
因此，当一个 Delivery 被删除时，引用它的 Packages 也将被删除。如果一个包裹被删除，任何交付都不会发生任何变化。
