CASCADING referential integrity in sql server

Cascading Referential integrity applies to the DELETE and UPDATE statements only because they cause changes to existing rows.
There are two actions that can take place for either operation:
 
1)CASCADE on DELETE
2)CASCADE on UPDATE
 
Lets Create two Tables,one is Parent Table with PRIMARY KEY and the Other is Child Table with FOREIGN KEY.
 

#Master TABLE :
CREATE TABLE EmpMaster(
EmpId INT PRIMARY KEY,EmpName VARCHAR(25)); 
 
#Child TABLE :
 
CREATE TABLE EmpDetails(
EmpId INT FOREIGN KEY REFERENCES EmpMaster(EmpId) 
ON DELETE CASCADE,
DeptId INT  PRIMARY KEY,
DeptName VARCHAR(20) );

 
Why CASCADE has to use?
When you try to delete or Update a Primary Key record without deleting or Updating the Foreign Key record the following Error will Raise.for example deleting a Parent Table Record of EmpId.To Avoid this type of error manually first you have to delete or update the Foreign Key column of Child Table then only delete or Update sql statement will execute on Parent Table.So to avoid this type of manually checking CASCADE is useful.
 

 
<strong>INSERT Reecords :</strong>
INSERT INTO EmpMaster(EmpId,EmpName) VALUES(1,'Kim')
INSERT INTO EmpMaster(EmpId,EmpName) VALUES(2,'Slaut')
INSERT INTO  EmpMaster(EmpId,EmpName) VALUES(3,'John')
 
INSERT INTO EmpDetails(EmpId ,DeptId ,DeptName ) VALUES(1,101,'AAA')
INSERT INTO EmpDetails(EmpId ,DeptId ,DeptName ) VALUES(2,101,'AAA')
INSERT INTO EmpDetails(EmpId ,DeptId ,DeptName ) VALUES(3,103,'CCC')

 
For example deleting a Parent Record of EmpId. In EmpDetails(child table) i am not deleting any child records..
 

DELETE FROM EmpMaster WHERE EmpId=1

 

 
You can see by using ON DELETE CASCADE on Foreign Key column you can delete the child table implicitly when deleting the parent table.No Error is raised here.
 
Use of ON UPDATE CASCADE :
 
update EmpMaster set EmpId=30 where EmpId=3

If the primary key for a record in the ‘EmpMaster’ table changes, all corresponding records in the’EmpDetails’ table must be updated using a cascading update.
 

UPDATE EmpMaster SET EmpId=30 WHERE EmpId=3


 
for example Updating a Parent Record of EmpId.In EmpDetails(child table) i am not Updating any child records..

Author: Pavan

I am asp.net developer have good knowledge of Asp.net ver 05,08,10 and good hand in sql server.Proficient in Object Oriented Programming and javascript, jQuery. Achievements - Integrate Spotfire, appnexus API ASP.net with sql server. Hobbies - Blogging ,Games, Movies ,Teaching,Keeping myself update with new technologies