Delete And Update Cascade in SQL Server

In this article, we learn about delete and update cascade in SQL Server.

• First I will create a two table: EMP table and EMP_Address table. And I will apply the primary key on both of the tables.

TABLE 1:- EMP

CREATE TABLE EMP(

ID INT PRIMARY KEY,

NAME NVarchar(50))

EMP TABLE

TABLE 2:- EMP_Address

CREATE TABLE EMP_Address(

ID INT PRIMARY KEY,

Address NVarchar(50))

EMP_Address TABLE

• After that, I will create a foreign key relationship with two tables. And Delete and Update cascade apply to that table.
• Query:

ALTER TABLE [dbo].[EMP_Address]

ADD CONSTRAINT FK_EMP_Address

FOREIGN KEY([ID])

REFERENCES [dbo].[EMP]([ID])

ON DELETE CASCADE

ON UPDATE CASCADE

INSERTED RECORD

DELETE CASCADE:

• In the delete cascade, If we delete the record from the source table also it will delete the record from another table.
• Query: DELETE FROM [dbo].[EMP] WHERE [Id]=1
• Run the query and check the records by using the select query. In both tables, the first record is deleted.

DELETE CASCADE

UPDATE CASCADE:

• In the update cascade, If we update the record from the table also it will update the record from another table.
• Query: UPDATE [dbo].[EMP] SET Id = 5 where [ID] = 4
• Check the tables again to see the record by using the select query.

UPDATE CASCADE

• After executing the table Id 4 is updated into 5 in both tables.

If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -