Cascading referential Integrity Constraint in SQL Server

Cascading referential Integrity Constraint

 Cascading referential integrity constraint allows to define the actions Microsoft SQL Server should take when a user attempts to delete or update a key to which an existing foreign keys points.

First Display the gender and employee1 tables. 

select * from gender

select * from employee1

check is there any foreign key present if not do following steps to add Foreign key REFERENCES

1. Here we need to add a genderID column under employee1 table

2. update all records genderID as 1.

3. Create genderID as a foreign key reference to the gender table.

1.

ALTER table employee1 add genderID Int

2.

update  employee1 set genderID = 1

update  employee1 set genderID = 2 where id = 2

3.

alter table employee1 add CONSTRAINT FK_employee1_gendarId foreign key (genderID) REFERENCES gender(Id)

Now display the all employee1 records

select * from employee1

The genderID columns value are references to the gendar table id

What if we want to remove the id = 1 record from the gender table? What value will be replaced under the genderID column in the employee1 table.

That action we will set by using cascading integrity constraints and that is taken by SQL server when this kind of scenario happens.

There are four actions.

1. No Action:
   - This is the default behavior. Whenever we delete or update the records 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. 

delete 

delete gender where id = 1//The DELETE statement conflicted with the REFERENCE constraint "FK_employee1_genderId". The conflict occurred in database "pratikshadb", table "dbo.employee1", column 'genderID'.

update gender set id = 6 where id = 1//The UPDATE statement conflicted with the REFERENCE constraint "FK_employee1_genderId". The conflict occurred in database "pratikshadb", table "dbo.employee1", column 'genderID'.

2. Cascade:
           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.

For doing cascade first we drop the foreign key constraint and then we add foreign key constraint with cascade.

​​alter table employee1 drop constraint FK_employee1_genderId

alter table employee1 add constraint  FK_employee1_genderId foreign key (genderID) REFERENCES gender(Id) on delete cascade

delete gender where id = 2

select * from employee1

select * from gender

If we check table employee1 then all record those gender id = 2 are deleted.

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.

​​alter table employee1 drop constraint FK_employee1_genderId

alter table employee1 add constraint  FK_employee1_genderId foreign key (genderID) REFERENCES gender(Id) on delete set null

alter table employee1 add constraint  FK_employee1_genderIdupdate foreign key  (genderID) REFERENCES gender(Id) on update set null

Check with update

update gendar set id = 6 where id = 1

select * from employee1

select * from gendar

Check with delete

update employee1 set gendarID = 3

select * from employee1

delete gendar where id = 3

select * from employee1

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.

​​alter table employee1 drop constraint FK_employee1_gendarId

​​alter table employee1 drop constraint FK_employee1_gendarIdupdate

update employee1 set gendarID = 6

alter table employee1 add constraint  FK_employee1_genderId foreign key (genderID) REFERENCES gender(Id) on delete set default

alter table employee1 add constraint  FK_employee1_genderIdupdate foreign key  (genderID) REFERENCES gender(Id) on update set default