CASCADING Referential Integrity in SqlServer

Syedshakeer
Posted by in Sql Server category on for Beginner level | Views : 14505 red flag
Rating: 4 out of 5  
 2 vote(s)

This article will expalins you how to delete and update a Child Table records implicitly when updating or deleting a Parenet Table.

Introduction


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.

CREATE TABLE EmpMaster
(
EmpId INT PRIMARY KEY,
EmpName VARCHAR(25)
);


CREATE TABLE EmpDetails
(
EmpId INT FOREIGN KEY REFERENCES EmpMaster(EmpId)
ON DELETE CASCADE,
DeptId INT  PRIMARY KEY,
DeptName VARCHAR(20)

);

Inserting Records

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')


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.

delete from EmpMaster where EmpId=3

Server: Msg 547, Level 16, State 1, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__EmpDetail__EmpId__147C05D0". The conflict

occurred in database "master", table "dbo.EmpDetails", column 'EmpId'.
The statement has been terminated.

update EmpMaster set EmpId=30 where EmpId=3

Server: Msg 547, Level 16, State 1, Line 1
The UPDATE statement conflicted with the REFERENCE constraint "FK__EmpDetail__EmpId__1293BD5E". The conflict

occurred in database "master", table "dbo.EmpDetails", column '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.

Explanation



1) Creating On DELETE CASCADE


One of  the Foreign Key Constraints uses ON DELETE CASCADE option may be added after the REFERENCES clause of CREATE TABLE command,as shown here

CREATE TABLE EmpDetails
(
EmpId INT FOREIGN KEY REFERENCES EmpMaster(EmpId)
ON DELETE CASCADE,
DeptId INT,
DeptName VARCHAR(20)

)

Use of  ON DELETE CASCADE

If CASCADE is specified, a row is deleted from the referencing table if that row is deleted from the parent table. Cascade Deletes all rows containing data involved in the foreign key relationship.Deleting a record in the 'EmpMaster' table, all corresponding Foreign Key records in the Employees table must be deleted

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

Lets see the result of both tables:

Select * from EmpMaster               Select * from EmpDetails

                  
   (Parent Table)                           (Child Table)

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.

2) Use of ON UPDATE CASCADE

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.

Creating the ON UPDATE CASCADE on Foreign Key Table:-


drop table empdetails


CREATE TABLE EmpDetails
(
EmpId INT FOREIGN KEY REFERENCES EmpMaster(EmpId)
ON UPDATE CASCADE,
DeptId INT,
DeptName VARCHAR(20)

)

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

update EmpMaster set EmpId=30 where EmpId=3

          Select * from EmpMaster        

                               

             

You can also create and use both ON DELETE and ON UPDATE CASCADE in a  foreing key column Table as follows

CREATE TABLE EmpDetails
(
EmpId INT FOREIGN KEY REFERENCES EmpMaster(EmpId)
ON DELETE CASCADE
On UPDATE CASCADE
,
DeptId INT,
DeptName VARCHAR(20)

)

 

Hope this article helps you lot and enjoyed it!

Page copy protected against web site content infringement by Copyscape

About the Author

Syedshakeer
Full Name: Syed Shakeer Hussiain P
Member Level:
Member Status: Member
Member Since: 2/5/2009 3:12:18 AM
Country: India
Syed Shakeer Hussain
http://www.dotnetfunda.com
Shakeer Hussain has completed his Master of Computer Applications degree from Deccan College of engg and technology of Osmania University.He is a MVM of www.dotnetspider.com.He has good experience in the areas of ASP.NET, C#.NET, VB.NET, SQL SERVER 2000/2005 and Windows Mobile. He has worked in Windows Mobile,Web Applicatin and ERP projects.

Login to vote for this post.

Comments or Responses

Posted by: manikandanpszsl-20650 on: 2/6/2013 | Points: 25
nice article,
very useful to add one new thing in my mind database.

Login to post response

Comment using Facebook(Author doesn't get notification)