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

User · Answer

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.

User · Answer

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

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

Examples related to sql

Examples related to database

Examples related to database-design

Examples related to cascade

Examples related to relation