SQL Server: Delete record from parent table when there is Foreign Key relation between parent and child tables

 SQL Server     sbo     Updated on Oct 27, 2013     VIKRAM     865   

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 NULLCHECKUNIQUEPRIMARY 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:

  Related Articles
Java Script: Create/Insert rows dynamically to HTML Table and Remove/Delete rows from HTML Table
Java Script: Create HTML Table dynamically
SQL Server: NOT NULL Constraint
SQL Server: Temporary Table
SQL Server: How to rename a column and How to rename a table
SQL Server: How to add a column to table and How to delete a column from table
ASP.Net: ListView Basics - ItemDeleting
SQL Server: How to create a table
  Recent Articles
Java Script: Create/Insert rows dynamically to HTML Table and Remove/Delete rows from HTML Table
Java Script: Create HTML Table dynamically
SQL Server: NOT NULL Constraint
SQL Server: Temporary Table
SQL Server: How to rename a column and How to rename a table
SQL Server: How to add a column to table and How to delete a column from table
ASP.Net: ListView Basics - ItemDeleting
SQL Server: How to create a table