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?
This question is related to
sql
database
database-design
cascade
relation
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;
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.
Source: Stackoverflow.com