SQL的删除级联，删除的方式是什么?

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 Course attribute in the BookCourses relation references the Code attribute in the Courses relation.

然后可以看到，BookCourses关系中的Course属性引用了Courses关系中的Code属性。

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 Courses relation, will it delete all referencing tuples in the BookCourses relation, or is it the other way around?

我的问题是，当这两个关系中的任何一个发生删除时，删除是通过什么方式发生的?如果我在课程关系中删除一个元组，它是否会删除BookCourses关系中的所有引用元组，或者是相反的?

Thank you for your time.

谢谢您的时间。

1 个解决方案

#1

158

Cascade will work when you delete something on table Courses. Any record on table BookCourses that has reference to table Courses will also be deleted.

当删除表课程上的内容时，Cascade会工作。任何有关桌面课程的记录也将被删除。

But when you try to delete on table BookCourses only the table itself is affected and not on the Courses

但是当你试图删除表上的BookCourses时，只有表本身受到影响，而不是在课程上。

follow-up question: why do you have CourseID on table Category?

后续问题:为什么你有课件类别的课程id ?

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;

#1

158