SQL Server Foreign Key Update And 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

 

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.

 

 

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.

 

 

After this, right-click on the Primary key icon. 

 

Now, the following options will appear.

 

 

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.

 

 

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.

 

 

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.

 

 

 

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.