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.