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

sql database database-design cascade relation

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?

One only wonders why the Categories table has a CourseID as the Primary Key while the Courses table has the EntryID. You seriously need to rethink your naming choices.

Please user proper column names to avoid confusion and clear DB structure.

Bhavesh G

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;

This answer has different table names and structures than the question... Making it far less useful.

@DanielBeardsley, I do not agree that this answer is not useful. That is if you read what it says. I do, however agree that the answer could be formatted so it’s clear what is part of the actual answer and what is another discussion. The schema highlighted above is related to the follow-up question but not the answer to the actual question.

Morten Holmgaard

Here is a simple example for others visiting this old post, but is confused by the example in the question and the other answer:

Delivery -> Package (One -> Many)

CREATE TABLE Delivery(
    Id INT IDENTITY PRIMARY KEY,
    NoteNumber NVARCHAR(255) NOT NULL
)

CREATE TABLE Package(
    Id INT IDENTITY PRIMARY KEY,
    Status INT NOT NULL DEFAULT 0,
    Delivery_Id INT NOT NULL,
    CONSTRAINT FK_Package_Delivery_Id FOREIGN KEY (Delivery_Id) REFERENCES Delivery (Id) ON DELETE CASCADE
)

The entry with the foreign key Delivery_Id (Package) is deleted with the referenced entity in the FK relationship (Delivery).

So when a Delivery is deleted the Packages referencing it will also be deleted. If a Package is deleted nothing happens to any deliveries.

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

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US