Foreign key constraints and Delete records

In many cases, while designing the database, we consider primary key and foreign key. Some of the cases, the data will be never deleted form the system and we use only soft delete which is nothing but keeping another column to indicate whether the row is active or not.

But some of the cases, we might want to delete the record but it will also have the reference in other table.

I will take an example of a table customer and items and itemlocation

create table itemlocation

(

locationid int primary key,

locationdesc varchar(100)

)

create table Items

(

ItemID int primary key,

ItemName varchar(100),

ItemPrice int,

locationid int constraint Item_location_FK references itemlocation(locationid)

)

create table customer

(

CustID int primary key,

CustomerName varchar(100),

ItemID int constraint Cust_Item_FK references Items(ItemID)

)

insert into itemlocation (locationid,locationdesc) values (1, 'Bangalore')

insert into itemlocation (locationid,locationdesc) values (2, 'Mysore')

insert into Items (ItemID, ItemName,ItemPrice,locationid ) values (1,'T shirt', 100, 1)

insert into Items (ItemID, ItemName,ItemPrice,locationid ) values (2,'Shoes', 500, 1)

insert into Items (ItemID, ItemName,ItemPrice,locationid ) values (3,'Shirt', 500, 2)

insert into customer(CustID, CustomerName, ItemID) values (1,'Subbu',null)

insert into customer(CustID, CustomerName, ItemID) values (2,'Ravi',1)

insert into customer(CustID, CustomerName, ItemID) values (3,'Raj',2)

insert into customer(CustID, CustomerName, ItemID) values (4,'Sam',3)

Now we have all the tables and data. If anyone has to delete the item, they need to delete referring customers first otherwise system will throw an error

delete from Items where ItemID=1

Msg 547, Level 16, State 0, Line 1

The DELETE statement conflicted with the REFERENCE constraint "Cust_Item_FK". The conflict occurred in database "ReportServer", table "dbo.customer", column 'ItemID'.

The statement has been terminated.

In some cases we can use delete cascade which will not throw this error and delete all referring records too. Here is what happens when we use delete cascade in our case

alter table customer drop constraint Cust_Item_FK

alter table customer add constraint Cust_Item_FK

foreign key (ItemID) references Items(ItemID)on delete cascade

go

Ok. Now you delete the same item and see what happens

Delete executed fine without any errors but you have also lost the customer record. If you look at customer table, the customer with ItemId 1 is deleted.

Now try the delete cascade on item table as below

alter table Items drop constraint Item_location_FK

alter table Items add constraint Item_location_FK

foreign key (locationid) references itemlocation(locationid)on delete cascade

go

After the table altered execute below statement

delete from itemlocation where locationid=1

Now see what happens, You have lost all the items and customers linked with location 1. We need to be very careful when we using delete cascade. All the data are lost.

To over come this problem we can use a new feature introduced in Sql 2005 and above on delete Set Null option.

Lets change our constraint something like this

alter table customer drop constraint Cust_Item_FK

alter table customer add constraint Cust_Item_FK

foreign key (ItemID) references Items(ItemID) on delete set null

Now lets try the delete statement as below

delete from Items where ItemID=1

Now the cstomer table has data as below

CustID CustomerName ItemID

----------- --------------- -----------

1 Subbu NULL

2 Ravi NULL

3 Raj 2

4 Sam 3

The Itemid which is deleted is affected on customer table but the customer is assigned with Null for the deleted item. We can also use on delete set default which will update default value while deleting referenced data.

This is one of the very good feature added on Sql which will definitely help many of us while deleting referenced data. So be careful to add this when you use delete cascade next time.