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;

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

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 be deleted automatically.

但是当您尝试删除表 BookCourses 时，只有表本身会受到影响，而不会影响 Courses

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

后续问题:为什么表 Category 上有 CourseID?

也许你应该将你的架构重组为这个，

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屋！