Use of Cascade in SQL Server

We know what Cascade is in SQL Server.
 
To explain this I will create two simple tables, employee and empsalary.

Table 1

CREATE TABLE employee (
id int not null ,
name varchar(30),
PRIMARY KEY (id)
)
insert into employee values(1,'RAVI')
insert into employee values(2,'ISHA')
insert into employee values(3,'SANTOSH')
insert into employee values(4,'PRAGYA')

Table 2

CREATE TABLE empsalary (
id int not null,
salary money
)
ALTER TABLE empsalary
alter CONSTRAINT FK1
FOREIGN KEY (id) REFERENCES employee(id)
insert into empsalary values(1,20000)
insert into empsalary values(2,1000)
insert into empsalary values(3,50000)
insert into empsalary values(4,70000)

The first table contains records of empid and empname. Depending on empid I will insert their salary into the second table.

I created a FOREIGN KEY on id in the second table. In other words, here only those records will be entered that exist in the first table with the same id.

Now we have two tables with references and some records. If the user wants to delete or update some records from the first table (employee) that references records that exist in the second table then SQL returns the following error.

Query

Delete from employee where id=2

Error

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK1". The conflict occurred in database "master", table "dbo.empsalary", column 'id'.
The statement has been terminated.

If you get the scenario above then you have a CASCADE property to do the task above.

Use the following procedure to enable Cascade:

Right-click on empsalary in the Object Explorer then select "Design" then right-click on the Id column then select "Relationships" then click on "FK1" then go to "Insert and Update specification".

Here see "Delete rule" and "Update Rule". By default both are in 'No Action'. Here you can select "Cascade".

Find the following image for reference:



After selecting Cascade save the table.

Now run the following query.

Delete from employee where id=2

Result

(1 row(s) affected)

I hope you find this article useful.

If there is any mistake in the above concepts then let me know in Comments. Comments would be appreciated. Thanks for reading.