SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?

cascade, database, database-design, relation, sql

Solution

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.

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

follow-up question: why do you have `CourseID` on table 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;

Problem

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. 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?

Original source