Simulating ON DELETE CASCADE in SQL Server

By:   |   Comments (2)   |   Related: > TSQL


A user recently asked about an interesting scenario involving tables with foreign key relationships. They wanted to generate the DELETE statements that would allow them to manually delete from child tables first (perhaps in stages), based on criteria they define, and tell them before performing the deletes how many rows the operation would affect in each table. They wanted output like this:

DELETE dbo.ChildTable1 WHERE ParentID < <some constant>;
-- This would delete 47 row(s).

DELETE dbo.ChildTable2 WHERE ParentID < <some constant>;
-- This would delete 14 row(s).

DELETE dbo.ParentTable WHERE ID < <some constant>; -- This would delete 11 row(s).

The catalog views, like sys.columns and sys.foreign_key_columns, have all the information we need to approach this problem.

Let’s create a simple schema; one parent table and two child tables:

CREATE TABLE dbo.Personnel
  ID           int PRIMARY KEY
); CREATE TABLE dbo.Addresses
  ID           int PRIMARY KEY,
  PersonnelID  int FOREIGN KEY REFERENCES dbo.Personnel(ID)
); CREATE TABLE dbo.Referrals
  ID           int PRIMARY KEY,
  ReferrerID   int FOREIGN KEY REFERENCES dbo.Personnel(ID),
  ReferreeID   int FOREIGN KEY REFERENCES dbo.Personnel(ID)

Notice that the second child table has two references back to the parent table.

Let’s insert some rows:

INSERT dbo.Personnel(ID) VALUES(1),(2),(3),(4),(5),(6);
INSERT dbo.Addresses(ID, PersonnelID) VALUES(1,1),(2,2),(3,3),(6,6);
INSERT dbo.Referrals VALUES(1,1,2),(2,4,3),(3,5,4),(4,5,2),(5,6,6);

Now, let’s say the user wants to delete all the rows from these tables, where the Personnel ID < 4. We can generate the desired output using a query against the catalog views, with the table and column name, which we can pass in as parameters to a stored procedure.

CREATE PROCEDURE   dbo.GenerateCascadingDeletes
  @ParentTable     nvarchar(512),
  @ParentColumn    sysname,
  @DeleteCriteria  nvarchar(255)
BEGIN   SET NOCOUNT ON;   DECLARE @sql nvarchar(max) = N'',
          @src nvarchar(max) = N'SELECT ''DELETE $t$ WHERE $c$ $clause$;'' UNION ALL SELECT
               ''-- This would delete '' + (SELECT RTRIM(COUNT(*)) FROM $t$ WHERE $c$ $clause$)
               + '' rows.'';';   -- generate deletes for child tables:   SELECT @sql = @sql
    + REPLACE(REPLACE(REPLACE(@src,N'$t$',t),N'$c$',c),N'$clause$',@DeleteCriteria)
    SELECT t = QUOTENAME(OBJECT_SCHEMA_NAME(pt.parent_object_id))
             + '.' + QUOTENAME(OBJECT_NAME(pt.parent_object_id)),
           c = QUOTENAME(
    FROM sys.foreign_key_columns AS pt
    INNER JOIN sys.columns AS pc
       ON pt.parent_object_id = pc.[object_id]
      AND pt.parent_column_id = pc.column_id
    INNER JOIN sys.columns AS rc
       ON pt.referenced_column_id = rc.column_id
      AND pt.referenced_object_id = rc.[object_id]
    WHERE pt.referenced_object_id = OBJECT_ID(@ParentTable)
      AND = @ParentColumn
  ) AS x;   -- final delete of parent table:   SELECT @sql += REPLACE(REPLACE(REPLACE(@src,N'$t$',@ParentTable),
    N'$c$',@ParentColumn),N'$clause$',@DeleteCriteria);   EXEC sys.sp_executesql @sql; END

Here is a sample run of the stored procedure.

exec dbo.GenerateCascadingDeletes 'Personnel', 'ID', '< 4'

Output from the above query:

DELETE [dbo].[Addresses] WHERE [PersonnelID]  < 4;
-- This would delete 3 rows.
DELETE [dbo].[Referrals] WHERE [ReferrerID]  < 4;
-- This would delete 1 rows.
DELETE [dbo].[Referrals] WHERE [ReferreeID]  < 4;
-- This would delete 3 rows.
DELETE dbo.Personnel WHERE ID  < 4;
-- This would delete 3 rows.

Notice how the counts are potentially misleading for the Referrals table; the first delete operation would delete one row based on the ReferrerID column, but the second operation would only be able to delete two rows based on the ReferreeID column (since one was already deleted).

That output could then be used to decide in which order to delete from the tables, based on importance, size, or other criteria. Some deletes could even be moved to a chunking process to minimize the impact to the transaction log. Once all child tables have been deleted, you can move on to the last statement, which deletes from the parent.

Note that this is a simple scenario and not a complete solution. Caveats include:

  • Multiple key columns are not supported (though it is possible to adjust this script to handle that).
  • Grandchildren are not supported (this would require recursion in some way and additional steps).
  • Circular references are not supported (this would have to be handled manually).
  • This is not protected from SQL Injection, but it could be enhanced to be more secure.


The catalog views provide all the information you need to automate activities like deleting from child tables before deleting from the parent. This solution has its limitations in more complex scenarios, but it can be a good starting point for the more typical use case of a simple parent-child relationship.

Next Steps

Read on for related tips and other resources:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog,, and also blogs at

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, April 11, 2024 - 3:24:21 AM - Pls Back To Top (92175)
This only works for simple cases, if there are many hierarchical level (for example CREATE TABLE dbo.UserEmail
ReferralsID int FOREIGN KEY REFERENCES dbo.Referrals(ID)
) the code can't delete UserEmail

Friday, March 22, 2024 - 12:02:44 PM - Lewis Back To Top (92104)
I just found it today, very userful :-)

But I wonder...can my DeleteCritera can be a sql command ?
For instance , I would like to delete by IDs selected from a select ......

get free sql tips
agree to terms