SQL Delete Cascade and Update Cascade with foreign key [2021]

SQL-DELETE-CASCADE
SQL Delete Cascade rule

In this article, we will learn SQL Delete Cascade rules with foreign key constraints having multiple examples.

Overview – SQL Delete Cascade

1.  SQL DELETE CASCADE : In the SQL server, when we create a foreign key using the SQL delete cascade option, it deletes all the related rows from the child table when the referenced row is deleted from the parent table having primary key constraint.

The following are the SQL scripts that create a parent (Departments), child (Employees) table, and a foreign key on the child table without the SQL delete cascade rule.

-- Create Departments table
Create table Departments
(
       Id int primary key identity,
       [DeptName] nvarchar(50)
)
Go
-- Create Employees table
Create table Employees
(
       Id int primary key identity,
       [Name] nvarchar(50),
	   [City] nvarchar(50),
       DeptId int constraint FK_Key foreign key references Departments(Id)
)
Go 
-- Insert records into Departments table
Insert into Departments values ('IT')
Insert into Departments values ('Sales')
Go 

-- Insert records into Employees table

Insert into Employees values ('Shekh Ali','New Delhi', 1),
                             ('Mark Adam','New York', 1),
							 ('Julie Folden','Paris', 2),
							 ('Amit Kumar','Mumbai', 2),
							 ('Priyanka','Noida', 2);

-- Select all the records from Departments and Employees table.
SELECT * FROM Departments;
SELECT * FROM Employees;

Once we run the above SQL script, the following “Departments” and “Employees” tables will get created in the database.

SQL-DEPARTMENTS-EMPLOYEES-TABLES

Delete related rows from the parent-child tables in SQL

When we delete a row from the Departments table, all the related rows from the Employees table must also be deleted.
For example, if we delete the IT department row from the Departments (Parent) table, we also want all the employees of the IT department to be deleted from the Employees (Child) table as well.

Here, the DeptId column in the Employees table is a foreign key that is referencing the Id (Primary key) column in the Departments table.

Let’s try to delete the IT department row from the Departments table by running the following SQL script.

-- Delete IT department
DELETE FROM Departments WHERE Id = 1;

If we try to delete a row from the Departments table which has related rows in the Employees table, by default, we get the following reference constraints error message:

 The DELETE statement conflicted with the REFERENCE constraint “FK_Key”. The conflict occurred in database “TestDB”, table “dbo.Employees”, column ‘DeptId’.
 
. The statement has been terminated.

SQL On Delete Cascade Example with Foreign Key Constraint

To use the SQL on delete cascade rule, let’s first delete the existing foreign key constraint from the Employees (Child) table. Following is the SQL script to drop the foreign key constraint.

-- Drop foreign key from the Employees table
ALTER TABLE Employees DROP CONSTRAINT FK_Key;

Now let’s recreate the foreign key constraint with cascading delete rule in the Employees table.

-- Create foreign key constraint with delete cascade
ALTER TABLE Employees
ADD CONSTRAINT FK_Dept_Emp_Cascade_Delete
FOREIGN KEY (DeptId) REFERENCES Departments(Id) ON  DELETE CASCADE;

Now when we delete a row from the Departments table, all the related rows from the Employees table will also get deleted.

-- Delete IT department
DELETE FROM Departments WHERE Id = 1;

-- Select all the records from Departments and Employees table.
SELECT * FROM Departments;
SELECT * FROM Employees;

Once we run the above SQL query, the IT department will get deleted from the Departments table, and also all the referenced rows will be deleted from the Employees table.

In the following image, we can see that the IT department is no more available in the Departments table and referenced rows from the Employees table will be cleaned up automatically.

sql-foreign-key-delete-cascade

On Update Cascade in SQL Server

SQL ON UPDATE CASCADE: When we create a foreign key constraint using ON UPDATE CASCADE, the referencing rows in the child table are updated when the referred row in the parent table with a primary key is modified.

On Update Cascade in sql query example

ALTER TABLE [dbo].[tblChild]
ADD CONSTRAINT FK_tblChild
FOREIGN KEY([ID])
REFERENCES [dbo].[tblParent]([ID])
ON UPDATE CASCADE

Conclusion:

In this article, we learned about Delete Cascade in SQL Server with examples. I hope you enjoyed and learned something from this article. if you have any suggestions, questions, or comments, please share them with us.

Reference: SQLShack, IBM

Recommended Articles

Leave a Reply