Cascading Referential Integrity Constraints in SQL Server Management Studio

In the Microsoft SQL server if we want to delete any record or column from one table but that record or column is a foreign key for another table then we will get the error to solve this problem we use Cascading referential integrity constraint.

It allows the actions that SQL Server should take when a user tries to delete or update a key to which an existing foreign key points. Suppose we have two tables, the first table’s name is“Student” and the second is “Department” as follows,

Student and Department Table

In the Student table “Roll_no” is the primary key which identifies each record uniquely and in the Department table “ID” is the primary key. Here the foreign key is Dept_ID in the Student table get the reference from the primary key ID from the Department table. 

In the Department table, if you delete the row with ID=1 then the records with Roll_no=1 and 2 from the Student table become an unsupported records which is also called as Orphan Record. Consequently, you won’t be able to determine the Department of this row. Therefore, Cascading Referential Integrity Constraint (CRI) can be used to specify what SQL Server should do in this situation. By default, the DELETE or UPDATE statement is rolled back and we receive an error.

Query:

DELETE from Department WHERE ID =1

Query

Output: 

output

To handle this type of error by using Cascading Referential Integrity constraint following actions can be performed.

Here are the options when setting up Cascading referential integrity constraints:

• No  Action: This is the default behaviour. An error is raised and the DELETE or UPDATE is rolled back if we attempt to delete or update a row whose key is referenced with existing rows in other tables.
• Cascade: Specifies that all rows containing those foreign keys are removed  if we attempt to delete or update a row with a key that is referenced by existing rows in other tables.
• Set NULL: Specifies that all rows containing those foreign keys are set to NULL if we attempt to delete or update a row with a key that is referenced by existing rows in other tables.
• Set Default: Specifies that all rows containing those foreign keys are set to a default value if an attempt is made to delete or update a row with a key referenced by existing rows in other tables.

By using the following steps we can perform the above operations or actions on the tables:

Step 1:  In SQL server management studio go to the Keys of Table and select Foreign  key :

Step 2:  After clicking “FK__student__Dept_ID__797309D9″  go to the Foreign  key Relationship:

Step 3:  In Foreign key Relationship click on INSERT And UPDATE Specification, and you will get the options such as No Action, Cascade, Set NULL, and Set Default.

From the above steps, we can use the designer of SQL Server Management Studio to set Cascading Referential Integrity Constraints for all the above mention actions. Also by using query you can you can specify cascading referential integrity constraints when creating a foreign key in SQL Server:

ALTER TABLE student
ADD FOREIGN KEY (Dept_ID) 
REFERENCES Department(ID)
ON DELETE CASCADE
ON UPDATE SET NULL;

Now if we set the Delete rule of Insert and update specification as cascade  and perform the DELETE operation on the Gender table then,

delete from Department where ID=1

select * from Student
select * from department

Output:   After Successful Completion of the Delete Query, the tables  will be:

Here you can see that the row with ID =1 from Department Table has been deleted and also the consecutive rows whose Dept_ID is 1 from the student table have also been Deleted. Finally Cascading Referential Integrity Constraints helps the user to delete or update the foreign key column data from one table and also change the consecutive table record also.