Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Using DELETE CASCADE Option for Foreign Keys

MSSQLTips author Ben Snaidero By:   |   Read Comments (9)   |   Related Tips: More > Database Design
Problem

Referential integrity is a very important thing to consider when designing a database. In my years as a DBA I've seen database designs that sit on both ends of the spectrum, none at all and cases where every table is linked to multiple tables. While the later certainly can be a little more difficult to work with it ensures the integrity of your data stays intact. The other end provides much more flexibility when it comes to updating and deleting data from your database, whether it's being done through the application or directly on the backend, but has the issue of possible orphan records if things are not done properly. This tip will look at the DELETE CASCADE option when creating foreign key constraints and how it helps keep the referential integrity of your database intact.

Solution

Before we get into the details of the DELETE CASCADE option I wanted to mention that there is another option you can use when creating foreign keys on tables, UPDATE CASCADE. I left out any details regarding this option from this tip as this is only necessary when the columns that are part of the foreign key are updated. In my past experience I've never had to update one of these columns since foreign keys are almost always created on key columns. I am sure there are cases where this is needed, but for this tip we are just going to focus on the DELETE CASCADE option.

Sample SQL Server Table Setup

For this example we will create two sample tables that have a foreign key relationship between them. The parent table has 99,999 records in it and the corresponding child table has 19 records for each record in the parent. Here is the DDL code:

-- Table creation logic
--parent table
CREATE TABLE [dbo].[Order](
 [OrderID] [bigint] NOT NULL,
 [OrderData] [varchar](10) NOT NULL,
 CONSTRAINT [PK_Order_1] PRIMARY KEY CLUSTERED 
    ([OrderID] ASC)
)
GO
-- child table
CREATE TABLE [dbo].[OrderDetail](
 [OrderDetailID] [bigint] NOT NULL,
 [OrderID] [bigint] NULL,
 [OrderData] [varchar](10) NULL,
 CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED 
    ([OrderDetailID] ASC)
)
GO
-- foreign key constraint
ALTER TABLE [dbo].[OrderDetail]  WITH CHECK 
ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Order] ([OrderID])
ON DELETE CASCADE
GO
-- data load
DECLARE @val BIGINT
DECLARE @val2 BIGINT
SELECT @val=1
WHILE @val < 100000
BEGIN  
   INSERT INTO dbo.[Order] VALUES(@val,'TEST' + CAST(@val AS VARCHAR))
   
   SELECT @val2=1
   WHILE @val2 < 20
   BEGIN  
      INSERT INTO dbo.[OrderDetail] VALUES ((@val*100000)+@val2,@val,'TEST' + CAST(@val AS VARCHAR))
      SELECT @val2=@val2+1
   END
   SELECT @val=@val+1
     
END
GO 

First Example

Now that we have some data, let's remove a record from the [Order] table. Here is the code. Note: I've added a "DBCC DROPCLEANBUFFERS" to each DML query to ensure there is no data in the cache before we run the statement.

DBCC DROPCLEANBUFFERS
GO
DELETE FROM [Order] WHERE OrderID=24433
GO

After running this statement we can query the [OrderDetail] table and verify that the records were removed. To get an idea of what needs to be done to if we did not have the DELETE CASCADE option set let's remove it and see what happens. Here is the code:

ALTER TABLE [dbo].[OrderDetail] DROP CONSTRAINT [FK_OrderDetail_Order]
GO
ALTER TABLE [dbo].[OrderDetail]  WITH CHECK 
ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Order] ([OrderID])
GO

Now let's run the following code, remembering that we have to delete the records from [OrderDetail] first as DELETE CASCADE option has been removed from the foreign key. You can see how the previous example is much simpler as it only requires one DELETE statement. Imagine if we had 5 or 6 tables with foreign keys into this parent table. To remove the parent record we would have to have a separate delete statement for each of these child tables.

DBCC DROPCLEANBUFFERS
GO
DELETE FROM [OrderDetail] WHERE OrderID=24032
DELETE FROM [Order] WHERE OrderID=24032
GO

Let's also take a look a the performance differences of these two approaches. Below is a SQL Profiler output from both DELETE scenarios. You can see from this trace using the DELETE CASCADE option also uses less resources than doing the DELETE with a separate statement.

DELETE CASCADE CPU (ms) Reads Writes Duration
Yes 281 12323 2 950
No 374 24909 3 1162

Second Example

One of the SQL Server best practices I follow is to always index any foreign key columns as they are always very heavily used both in WHERE clauses and to join tables. Let's add an index to the [OrderDetail] table and run through the same scenario as in the example above. Here is the DDL code for the index:

CREATE NONCLUSTERED INDEX IX_OrderDetail_OrderID ON dbo.[OrderDetail] (OrderID)
GO

And here is the new code for the DELETE statements along with the statement to remove the DELETE CASCADE OPTION. Note: All that is changed here is the OrderIDs

DBCC DROPCLEANBUFFERS
GO
DELETE FROM [OrderDetail] WHERE OrderID=90032
DELETE FROM [Order] WHERE OrderID=90032
GO
ALTER TABLE [dbo].[OrderDetail]  WITH CHECK 
ADD CONSTRAINT [FK_OrderDetail_Order] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Order] ([OrderID])
ON DELETE CASCADE
GO
DBCC DROPCLEANBUFFERS
GO
DELETE FROM [Order] WHERE OrderID=90433
GO

Looking at the results below we can see that with proper indexing we see almost no performance difference between the two methods. As I mentioned above though, using the DELETE CASCADE option does have the benefit of keeping your SQL code much simpler in that you only have to delete from the top level parent and all the child data is cleaned up automatically.

DELETE CASCADE CPU (ms) Reads Writes Duration
Yes 0 300 7 79
No 0 312 6 64
Next Steps


Last Update: 8/1/2012


About the author
MSSQLTips author Ben Snaidero
Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, March 17, 2014 - 4:42:22 PM - benjimari Read The Tip

I didn't get what happened,. I hope you take this comment in a good way but the way you explain is the least effective I found, it didint even clearly tell what exactly is the thing this topic solve. Here's an example from codeproject with a good explanation of the use of the delete cascade topic "Use the ON DELETE CASCADE option if you want rows deleted in the child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it."

 
I will try to visit this site again I hope when that happen it will not be as confusing as today. Thanks you

Thursday, August 16, 2012 - 6:52:23 AM - Rakish Magehm Read The Tip

thanks for this tutorials was very helpful thank you bless you thank you so much

 

how to find the sql server management studio in the windows system files thank you so much for help on the matter

 

i have to education for my new job myself in united states

 

sincerely,

Rakash Maghaderjshiaskm Lamakashapharbaim.

 

your kindness is thank you


Tuesday, August 14, 2012 - 11:16:48 PM - Ben Snaidero Read The Tip

Hi Vikas,

Sorry I missed one thing in your original question, when you say the trigger performs better do you mean you use a trigger on the master table to handle the deletes on the other tables through this?

Given the way you’ve explained your schema layout, specifically the clustered indexes on the 9 tables, I think this does produce the least amount of contention. Only suggestion I would have if you’re looking to maximize performance would be to try handling the deletes manually and not use a trigger/cascade at all. In any case though I don’t think deleting 10,000 records (whichever method you are using) would/should be very slow. Out of curiosity how long does it take? If you try doing the delete manually and it’s a lot quicker could your tempdb be on a different type of disk than the data and log files (something slower perhaps) and the eager spool is causing your slowness as the result of the spool is stored there.

And to answer your question regarding why the eager spool. I believe it’s required during the DELETE CASCADE to keep a consistent view of the table during the DELETE operation and will block until it’s complete but since it’s doing an index seek it shouldn’t have any effect on accessing other records in the table that are not being affected by the delete.

Ben


Tuesday, August 14, 2012 - 12:50:43 PM - Vikas Read The Tip

Hello Ben,

There are some deletes that happen throught the day but I do have a nightly purge of 50K+ but in batches of 10K (yes minimum deleted in single Purge is 10K - not individual 10K deletes, have to delete in batches of 10K, as I don't want to generate too much volume for the database is replicated, so run in a loop and delete 10 K as a single maximum per loop).

Yes the remaining 9 tables are clustered and part of the key is originating from the Master as a result I am making use of the Cascade Delete on the Master.

Thanks again for your time.

Vikas


Tuesday, August 14, 2012 - 9:28:07 AM - Ben Snaidero Read The Tip

Hi Vikas,

A couple quick questions.  When you do the delete on the master table is it a single statement that deletes the 50K+ records or 50K+ individual statements?  Also, are the indexes on the 9 tables clustered?

Ben


Tuesday, August 14, 2012 - 5:58:39 AM - Srikrishna Read The Tip

Good article


Monday, August 13, 2012 - 8:09:39 PM - Vikas Read The Tip

Hello Ben,

Let me expalin my case with a scenario, say we have 10+ tables and one of these is the Master table (that is it has the PK, while the remaining have FK's to this Table). I do have CASCADE delete turned on and also have a Index in place. Also lets assume the tables have a million+ rows and each of them receive new 500K rows a day.

Now in such a scenario, when I delete 50K+ rows in a day, I see an eager spool on the 9 tables along with Index Seek (on the FK's Index). As a result am not getting any gain in performance rather am seeing scenarios where the trigger performs better than the Cascade for higher volumes. Am not sure how/ the reason behind an Eager spool in the plan.

Is there anything else we can do to perform Purge operation on tables in an efficient manner? To minimize down time/ improve availability of tables (avoid blocking etc.). I am exploring the possibility of using Partitioning to do so... really not sure if it is the best way though.

Any thoughts or suggestions are welcome.

Thanks for your time,

Vikas


Wednesday, August 01, 2012 - 12:00:10 PM - Ben Snaidero Read The Tip

Hi Allen,

It's not a typo.  When I mention there is almost no performance difference I mean between using delete cascade and deleting the records from each table with individual statements.  You are correct in that there is a big performance benefit with the index vs without.

Thanks for reading

Ben.


Wednesday, August 01, 2012 - 9:51:44 AM - Allen Wang Read The Tip

Is it a typo? In your article said after added index on foreign key "Looking at the results below we can see that with proper indexing we see almost no performance difference between the two methods. ". But, it is a big difference between two reading, CPU, Reads, Writes and Duration all dropped down after index was being added.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.