Oliver Spryn Oliver Spryn - 3 months ago 14
SQL Question

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;


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?

Thank you for your time.

Answer

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.

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;