SQL ON DELETE CASCADE，删除发生在哪个方向？ [英] SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?

问题描述

如果我在数据库中有两个关系，像这样：

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;

然后你可以看到 Course 在 Courses 中的代码关系。

Then you can see that the Course attribute in the BookCourses relation references the Code attribute in the Courses relation.

我的问题是当两个关系之一发生删除时，删除级联是哪种方式？如果我在 Courses 关系中删除一个元组，它是否会删除 BookCourses 关系中的所有引用元组，

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?

谢谢您的时间。

推荐答案

当您删除表 Courses 上的某些内容时，Cascade会正常工作。表 BookCourses 中引用表 Courses 的任何记录也将被删除。

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.

但是当你尝试在表 BookCourses 上删除时，只有表本身受影响，不在课程

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

后续问题：为什么在表类别上有 CourseID em>

也许你应该将架构重新组织成这个，

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;

这篇关于SQL ON DELETE CASCADE，删除发生在哪个方向？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！