Introduction
In this article we will discuss Cascading Referential Integrity Constraints in SQL Server. SQL Server provide you to define cascading referential integrity constraints at table creation time or alter table. Let’s go ahead and discuss the same with proper real-time example.
What are Referential Integrity in SQL Server?
In the Microsoft SQL server if you want to delete any record or column from one table but that record or column is a foreign key for another table then you will get the error to avoid this problem you can use Cascading referential integrity constraint.
It allows to define the action by Microsoft SQL server should act when a user attempts to delete or update a key to which an existing foreign key.
When you delete row from parent tables records then depending records from child tables become an orphan record. Without Cascading Referential Integrity, we get an error and the delete or update statement is rollback.
Example-
ALTER TABLE EMPLOYEE
add
CONSTRAINT EMP_DEPTID_DEPT_ID_FK
FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(ID)
ON DELETE NO ACTION/CASECADE/SET NULL/SET DEFAULT
ON UPDATE NO ACTION/CASECADE/SET NULL/SET DEFAULT
)
Please read more details about our previous topic Identity Column in SQL
Now we will discuss each keyword details and will know what is the use.
No Action:-
This is the default behaviour. No action specifies that if an attempts is made to delete or update a row with a key reference by foreign keys in existing rows in other tables. That time, it will raise an error and the delete or update will rollback.
Cascade:-
Specifies that if an attempts 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.
Set Null:-
Specifies that if an attempts 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
Set Default:-
Specifies that if an attempts 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 value.
