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

By: Ben Snaidero

Overview

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.

Explanation

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

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.

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]

ALTER TABLE [dbo].[Child]  WITH CHECK 
ADD CONSTRAINT [FK_Child_Parent] FOREIGN KEY([ParentID])
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.

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.

Additional Information

• Using the DELETE CASCADE option for foreign keys
• The importance of SQL Server foreign keys
• You can't define a foreign key constraint that contains multiple cascade paths