Search⌘ K
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Cascading Delete

Explore how to implement cascading deletes in SQL to automatically remove dependent records. Understand altering tables, foreign key constraints, and the importance of ON DELETE CASCADE in managing referential integrity.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- The query to enforce referential integrity constraints
SELECT * FROM Skills;
ALTER TABLE    Skills
ADD CONSTRAINT FK_EmpID
FOREIGN KEY    (EmpID) REFERENCES Employees(EmpID)
ON DELETE      CASCADE;
DELETE FROM Employees
WHERE       EmpID = 3;
SELECT * FROM Skills;

Code explanation

The explanation of the solution code is given below:

  • Line 2: This retrieves all the values from the Skills table.

  • Lines 4–7: The ALTER TABLE statement alters the Skills table to add a FOREIGN KEY constraint named FK_EmpID. It links EmpID in Skills to EmpID in Employees, with ON DELETE CASCADE to remove associated skills if an employee is deleted.

  • Lines 9–10: This deletes the employee with EmpID = 3 from the Employees table.

  • Line 12: This retrieves all the values from the Skills table again, reflecting the changes after the employee deletion.

Recalling relevant concepts

We have ...