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

Question

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?

Answer 1

When you are deleting something on table Courses then the Cascade will work.

Any row on table BookCourses that has the reference to table Courses can be deleted automatically.

If you are trying to delete on table BookCourses only then the table itself is affected and not on the Courses.

If the question arises like why do you have CourseID on table Category?

Then 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;