SQL Interview Preparation—Intermediate Level/

...

/

Answer: Cascading Delete

Answer: Cascading Delete

Find a detailed explanation about using referential integrity constraints.

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 ...