Cascading referential integrity constraint in sql ~ MiTechDev.com

Mitechdev.com is a programming blog which focused on Web Programming and development

Wednesday, 8 March 2017

Cascading referential integrity constraint in sql

06:05:00 Posted by raviteja swayampu , No comments
In this tutorial i am going to explain about Cascading referential integrity constraint in sql server.Before going into this article check previous two article in Sql server learning series for beginners.They are as follows,
1.Creating and Working with Tables in sql server
2.Default 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.

However, you have the following options when setting up Cascading referential integrity constraint.

1. No Action: This is the default behaviour. No Action 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, an error is raised and the DELETE or UPDATE is rolled back.

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.

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.

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.

Lets's check this Cascading referential integrity constraint(1.cascade on delete and cascade on update) using a example..
1.Create two tables and insert some data..
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')

when i try to execute below statement statement in sql server i got this error like this
delete from EmpMaster where EmpId=3

Error:
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__EmpDetail__EmpId__0DAF0CB0". The conflict occurred in database "TestDB", table "dbo.EmpDetails", column 'EmpId'.

The statement has been terminated.

Note: Similarly we also get error while updating the values of EmDetails column EmpId..For this we go for this constraints to eliminate the errors..
 1.Cascading on DELETE:
One of the Foreign Key Constraints uses ON DELETE CASCADE option which 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)
)
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
if i run this query the row with EmpId=1 in EmpDetails also deleted.
mitechdev.com

Note: 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:
CREATE TABLE EmpDetails
(
EmpId INT FOREIGN KEY REFERENCES EmpMaster(EmpId) 
ON UPDATE CASCADE,
DeptId INT,
DeptName VARCHAR(20)
)
Ex: execute below statement below
Update EmpMaster set EmpId=30 where EmpId=3
mitechdev.com

0 comments:

Post a Comment