mssqltips logo

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



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


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]

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.

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

Last Update: 2/17/2014




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools