SQL Server Foreign Key Update And Delete (Cascading Referential Integrity)

In this article, you will learn about SQL Server Foreign Key Delete (Cascading Referential Integrity).

What is Cascading Referential Integrity?

 
When a user tries to delete a key (column) on which an existing foreign key is based, that is called "Cascading Referential Integrity".
 
Prerequisites to understand this are: 
  1. Create tables
  2. Add primary and foreign keys to those tables
  3. Add default constraints
Example
 
Let us understand this by an example of two tables - tblDepartment and tblEmployee.
 
tblDepartment
tblEmployee
 SQL Server Foreign Key Update And Delete (Cascading Referential Integrity)  SQL Server Foreign Key Update And Delete (Cascading Referential Integrity)
 
Problem
 
Now, if you delete a record from tblDepartmentwhere ID = 1 and it is having a row with ID = 1 and 6 in tblEmploye table with the foreign key.
 
SQL Server Foreign Key Update And Delete (Cascading Referential Integrity)
 

In case we use the cascading referential integrity

 
When we try to DELETE this record, we get an error and the DELETE or UPDATE statement is rolled back.
 
Solution
 
Please apply the following ways to delete the records.
 
Right-click on the tbldepartment >> Design.
 
SQL Server Foreign Key Update And Delete (Cascading Referential Integrity)
 
After this, right-click on the Primary key icon. 
 
Now, the following options will appear.
 
SQL Server Foreign Key Update And Delete (Cascading Referential Integrity)
 

Set Delete Rule

 
This is one of the main key items that we have to discuss in detail. There are 4 delete rules:
  1. No Action
  2. Cascade
  3. Set Null
  4. Set Default
Rule 1 - No Action
 
This is the default action.
No Action specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE or UPDATE is rolled back.
 
SQL Server Foreign Key Update And Delete (Cascading Referential Integrity)
 
Rule 2 - Cascade
 
This specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted or updated.
 
SQL Server Foreign Key Update And Delete (Cascading Referential Integrity)
SQL Server Foreign Key Update And Delete (Cascading Referential Integrity)
 
Rule 3 - Set NULL
 
Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.
 
SQL Server Foreign Key Update And Delete (Cascading Referential Integrity)
 
SQL Server Foreign Key Update And Delete (Cascading Referential Integrity)
 
Rule 4 - Set Default
 
Specifies that if an attempt is made to delete or update a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to default values.