Cascading referential integrity in sql server

What is Cascading referential integrity and when we use it

Can we delete the parent records, while child record exists (in primary key and foreign relation exists in tables),

Answer is Yes, It can be done with Cascade (Cascading Referential integrity)
Cascading Referential integrity applies to the DELETE and UPDATE statements only because they cause changes to existing rows.

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 tbl_Emp
(
EmpId INT PRIMARY KEY,
EmpName VARCHAR(25)
); 

CREATE TABLE tbl_EmpDetails
(
EmpId INT FOREIGN KEY REFERENCES tbl_Emp(EmpId)
ON DELETE CASCADE,
DeptId INT  PRIMARY KEY,
DeptName VARCHAR(20)
);


----------Enter records - execute below queries----------------------

insert into tbl_Emp (EmpId,EmpName) values(1,'rohit')
insert into tbl_Emp (EmpId,EmpName) values(2,'shiv')
insert into  tbl_Emp (EmpId,EmpName) values(3,'chetan')


insert into tbl_EmpDetails (EmpId ,DeptId ,DeptName ) values(1,101,'AAA')
insert into tbl_EmpDetails (EmpId ,DeptId ,DeptName ) values(2,101,'BBB')
insert into tbl_EmpDetails (EmpId ,DeptId ,DeptName ) values(3,103,'CCC')

If you try to delete or update primary record, It will show error.

delete from tbl_Emp where EmpId=1

Server: Msg 547, Level 16, State 1, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__ tbl_EmpDetails__EmpId__147C05D0".
Same as above, error will come on update statement.

This error is coming because there are child records in the tbl_EmpDetails. To remove this error either you have to first delete records manually from Child table or use Cascade reference.

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

Same for update

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

You also can use Delete and update.

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

If table already exists without cascade, then you may add cascade with below query

ALTER TABLE tbl_EmpDetails
ADD CONSTRAINT EmpId
FOREIGN KEY (EmpId)        
REFERENCES tbl_Emp(EmpId)
ON DELETE CASCADE;

Click here to go on website