By: Ben Snaidero | Comments (11) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > 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
- Syntax for creating foreign key constraints
- SQL Server will not let you create a constraint that has multiple cascade paths
- Read more information about other cascade options
About the author
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips