Using DELETE CASCADE Option for Foreign Keys

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.

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.

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