Cascading referential integrity in SQL Server

Cascading referential integrity constraints are foreign key constraints that is used to defines the action that SQL Server performs when a user try to delete or update a primary key value to which an existing foreign key points.

There are two option that can be used with Foreign key as follows :

DELETE CASCADE: When a foreign key is created with DELETE CASCADE option, it deletes the referencing rows in the child table automatically, when the referenced row is deleted in the parent table which has a primary key.

UPDATE CASCADE: When a foreign key is created with UPDATE CASCADE option, the referencing rows are updated automatically in the child table, when the referenced row is updated in the parent table which has a primary key.

 

CREATE TABLE dbo.TBLA(
ID INT PRIMARY KEY, NAME VARCHAR(30)
)
INSERT INTO dbo.TBLA(ID, NAME) VALUES (1, 'RAJESH KUMAR'),
(2, 'MANOJ SINGH')

CREATE TABLE dbo.TBLB(
ROW_ID INT, ADDRESS VARCHAR(50), 
ID INT REFERENCES dbo.TBLA  (ID)
) 

INSERT INTO dbo.TBLB(ROW_ID, ADDRESS, ID) VALUES
(1, 'STREET NO. 6, FLOOR #3',1),
(2, 'STREET NO. ,7 FLOOR #4',2)

Lets look at an example on Cascade in SQL Server.

As you can see, Here we have a child table TBLB which has a foreign key that is referencing a rows from TBLA which has a primary key so if we try to delete or update rows in parent table TBLA that is referenced by TBLB. It gives an error saying ‘conflicted with the REFERENCE constraint’

Lets see, what happen if you try to delete, or update records in parent table that is referenced by child table

Following statement uses update command which updates the value for ID =1 in TBLA, which is referenced by child table TBLB.

As you can see below, it gives an error that means you can not update any row in parent table that is referenced by child table TBLB.

 

 

Following statement uses delete command which deletes the record for ID =1 in table TBLA, which is referenced by child table TBLB.

As you can see below, it gives an error that means you can not update any row in parent table that is referenced by child table TBLB.

 

As you have seen above, it shows that foreign key maintain the referential integrity between table records that why it does not allow you to delete or update any rows directly in parent table that is referenced by child table.

So for this scenario, when you create a foreign key with CASCADE DELETE, or CASCADE UPDATE option, you can update, or delete any record directly in parent table and referenced rows records in child table is deleted, or updated as well.

 

FOREIGN KEY WITH CASCADE DELETE

As you have already created a foreign key on TBLB,  so to add CASCADE DELETE option, you need to alter table to add foreign key with DELETE CASCADE option.

Use following statement for this.

ALTER TABLE TBLB ADD CONSTRAINT FK_CASCADE_DELETE FOREIGN KEY(ID)
REFERENCES dbo.TBLA(ID)
ON DELETE CASCADE

You can quickly check and verify whether the foreign key is created with CASCADE DELETE  or not, using SP_HELP SP.

SP_HELP TBLB

As you can see above screenshot, Foreign key is created with CASCADE DELETE option that can be seen in delete_action column.

Lets try to delete any record from parent table TBLA that is referenced by child table TBLB.

As you can see below output, ID=1 in parent table TBLA  is referenced by child table TBLB.

SELECT * FROM TBLA
SELECT * FROM TBLB

 

 

 

 

 

 

 

Lets delete the record for ID =1 from parent table TBLA.

DELETE FROM TBLA WHERE ID =1

You will notice, that it is deleted without giving any error even though it is referenced by child table.

Now lets check the child table to verify whether it is deleted from child table too.

SELECT * FROM TBLA WHERE ID=1

SELECT * FROM TBLB WHRE ID=1

As you can see above result set, the same record for Id =1 in child table is deleted automatically.

 

FOREIGN KEY WITH CASCADE UPDATE

Lets alter the table again and add foreign key with CASCADE UPDATE option, using following statement.

ALTER TABLE TBLB ADD CONSTRAINT FK_CASCADE_UPDATE FOREIGN KEY(ID)
REFERENCES dbo.TBLA(ID)
ON UPDATE CASCADE

You can verify whether the Foreign key with  CASCADE UPDATE option is created or not using SP_HELP SP.

SP_HELP TBLB

As you can see above output of SP_HELP SP, foreign key is created with CASCADE UPDATE option, that can be seen in update_action column.

 

Lets  look at the records that we have in TBLA and TBLB 

SELECT * FROM TBLA 

SELECT * FROM TBLB

Now try to update any record in parent table TBLA that is referenced by child table TBLB.

As you can see above output of table,  for ID=2 in parent table is referenced by child table.

Lets update the value for ID column that is 3 in parent table.

UPDATE TBLA SET ID =3 WHERE ID =2

 

As you can see, it is updated without giving any error.

Now Lets check the both table to verify records.

SELECT * FROM TBLA 

SELECT * FROM TBLB

As you can see, once you updated the value for id =2 in parent table, a referenced row is also updated in child table.

Now value for id is 3 in both tables.

 

Also Read.

SQL Foreign key 

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 2,822 total views,  1 views today