How delete record from parent table when there is Foreign Key relation in SQL Server

Introduction:

In this article i will explain how to delete record from parent table when there is Foreign Key relation between parent and child tables in SQL Server.

Description:

In previous articles i explained what is Constraint, how many Constraints available in SQL Server, and what is NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and DEFAULT Constraint in SQL Server. Now i will explain how to delete record from parent table when there is Foreign Key relation between parent and child tables in SQL Server.

Before reading this article, read article on "FOREIGN KEY Constraint"

Delete from Department where DeptNo = 101.

delete from Department where DeptNo = 101

After executing the above statement, we got the following error:

The DELETE statement conflicted with the REFERENCE constraint "FK_DeptNo". The conflict occurred in database "TestDB", table "dbo.Employee", column 'DeptNo'.

To avoid this, before creating FOREIGN KEY on child table we will put some additional conditions like:

• on delete cascade
• on update cascade
• on delete set null
• on update set null

on delete cascade:

By this the child table records which are related to the parent table are deleted when the particular record is deleted from parent table.

Now i'm creating Department, and Employee tables.

Department:

-- Create 'Department' table
Create table Department
(
    DeptNo int primary key identity(101,1),
    DeptName varchar(50)
)

-- Insert data into 'Department' table.
insert into Department values('Finance')
insert into Department values('Production')
insert into Department values('Marketing')

-- Select data from 'Department' table.
select * from Department

Output:

Employee:

-- Create 'Employee' table
Create table Employee
(
    EmpNo int primary key identity(1,1),
    EmpName varchar(50),
    EmpSalary decimal,
    DeptNo int constraint FK_DeptNo references Department(DeptNo)
    on delete cascade
)

-- Insert data into 'Employee' table.
insert into Employee values('AAA', 1000, 101)
insert into Employee values('BBB', 2000, 102)

-- Select data from 'Employee' table.
select * from Employee

Output:

Delete from Department where DeptNo = 101. Here all the 101 records will be deleted from both tables(parent and child).

delete from Department where DeptNo = 101
select * from Department
select * from Employee

Output:

on delete set null:

By this the child table records which are related to the parent table are updated with NULL when the particular record is deleted from parent table.

Now I'm re creating Department, and Employee tables.

Department:

-- Create 'Department' table
Create table Department
(
    DeptNo int primary key identity(101,1),
    DeptName varchar(50)
)

-- Insert data into 'Department' table.
insert into Department values('Finance')
insert into Department values('Production')
insert into Department values('Marketing')

-- Select data from 'Department' table.
select * from Department

Output:

Employee:

-- Create 'Employee' table
Create table Employee
(
    EmpNo int primary key identity(1,1),
    EmpName varchar(50),
    EmpSalary decimal,
    DeptNo int constraint FK_DeptNo references Department(DeptNo)
    on delete set null
)

-- Insert data into 'Employee' table.
insert into Employee values('AAA', 1000, 101)
insert into Employee values('BBB', 2000, 102)

-- Select data from 'Employee' table.
select * from Employee

Output:

Delete from Department where DeptNo = 101. Here all the 101 records will be deleted from parent table but not from child table. DeptNo will be updated with NULL.

delete from Department where DeptNo = 101
select * from Department
select * from Employee

Output: