Use DELETE CASCADE Option to Handle Child Key Removal in Foreign Key Relationships


Using the DELETE CASCADE option in your foreign key constraint definitions means better performance and less code when removing records from tables that have a parent-child relationship defined.


Let's first confirm that our current schema does indeed have the DELETE CASCADE option defined on the foreign key between the Parent and Child table. Here is the SQL statement the check this as well as the result.

SELECT name,delete_referential_action_desc
  FROM sys.foreign_keys

name delete_referential_action_desc
FK_Child_Parent CASCADE

Now that we've confirmed we have this option defined let's delete a record from the Parent table using the following SQL statement.

DELETE FROM [dbo].[Parent] where ParentID=82433

Looking at the explain plan for this query we want to note that the SQL Optimizer is first removing the child records then performing the delete on the Parent table. Because of this it only needs to access each table once.

Explain Plan - Cascade Delete

Now let's remove the DELETE CASCADE option from our foreign key definition and see if there are any differences. In order to do this we'll need to drop and recreate the foreign key without the DELETE CASCADE option. Here are the SQL statements to make this change.

ALTER TABLE [dbo].[Child] DROP CONSTRAINT [FK_Child_Parent]

REFERENCES [dbo].[Parent] ([ParentID])

Once the foreign key has been recreated we can run a second delete to see if there is any difference in performance. One thing to note here is that without the DELETE CASCADE option defined we need to run an additional delete statement to remove the records from the Child table first. Here are the SQL statements to perform the delete.

DELETE FROM [dbo].[Child] where ParentID=62433
DELETE FROM [dbo].[Parent] where ParentID=62433

Looking at the explain plan for these statements we see that they are quite similar. The only difference being that because we are executing separate delete statements the Child table needs to be accessed a second time to check the foreign key constraint when deleting from the Parent table.

Explain Plan - No Cascade Delete

Using the SQL Profiler results from each query we can confirm this extra scan of the Child table does indeed mean that the DELETE CASCADE option performs better. We can see below that the DELETE CASCADE option uses less resources in every category and runs about 20% faster.

CPU Reads Writes Duration
No Delete Cascade 344 28488 0 399
Delete Cascade 250 14249 0 312
Additional Information

Comments For This Article

get free sql tips
agree to terms