Temporarily turn on Cascade Delete

Question

This has probably been asked before, but I couldn’t find the answer.

I have a complex datamodel in sql-server-20XX (more likely to be something old) in which cascade delete has been turned off (by design).

A large chunk of our database has been sold (I can make the distinction on the top-level) and there are reasons why we want to delete that data. I want to do that in a single script, in which I first turn on cascade delete, than delete all the data, than turn off cascade delete again, but I can’t find the correct syntax.

Relevant things to mention:

I want to delete about 40% of the rows from 10-15 tables in total, that are all somehow 1:n-1:n…-coupled to the Organisations-table
Performance is not relevant, since the application is not used outside of business hours

The tables must stay, I need to get rid of 10/33 rows in the Organisations-table and I need to get rid of ALL the rows in Orders, users… etc that are coupled to those organisations

asked 2022-05-20 by Ward

Answer

SQL Server doesn’t let you ALTER CONSTRAINT to alter an existing constraint to cascade on delete. However, SQL Server will happily let you create identical Foreign Key constraints. So rather than having to mess with your existing FK constraints, you can simply create some new ones, which will help you do your cleanup, then you can drop those special-purpose constraints when you’re done.

Example data

Consider this very simple schema:

CREATE DATABASE [CascadeOnDelete];
GO

USE [CascadeOnDelete];

CREATE TABLE dbo.Customers (
    CustomerID int,
    CustomerName nvarchar(100),
    CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (CustomerID)
    );
INSERT dbo.Customers (CustomerID, CustomerName)
VALUES (1,'Grandpa Joe'),(2,'Grandma Josephine'),
        (3,'Grandpa George'),(4,'Grandma Gerorgina'),
        (5,'Charlie Bucket');

CREATE TABLE dbo.Orders(
    OrderID int,
    CustomerID int
        CONSTRAINT FK_Orders_CustomerID 
        REFERENCES Customers(CustomerID),
    OrderDetails nvarchar(100),
    CONSTRAINT PK_Parents PRIMARY KEY CLUSTERED (OrderID)
    );
INSERT dbo.Orders (OrderID, CustomerID, OrderDetails)
VALUES (1,1,'Wonka bar'),
       (2,5,'Wonka bar'),
       (3,5,'Wonka Scrumdiddlyumptious Bar');

SELECT * FROM Customers;
SELECT * FROM Orders;

If you try to delete Grampa Joe, you’ll get an error because Grampa Joe has an order:

DELETE c
FROM dbo.Customers AS c
WHERE CustomerID = 1;

/*
Msg 547, Level 16, State 0, Line 33
The DELETE statement conflicted with the REFERENCE constraint &quot;FK_Orders_CustomerID&quot;. The conflict occurred in database &quot;CascadeOnDelete&quot;, table &quot;dbo.Orders&quot;, column 'CustomerID'.
*/

Now that we have that set up, we can script out the rest of what we need:

The solution

First, we can use some dynamic SQL to generate the script for every FK in your database. I’m appending the names with _CASCADEDELETES to identify them. I’m also creating these WITH NOCHECK and ON DELETE CASCADE regardless of the original FK definition. You would take the output of this script, review it, and run it to duplicate every FK in your database with these additions:

--adapted from https://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/
SELECT N'ALTER TABLE ' 
       + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) + N' WITH NOCHECK'
       + N'    ADD CONSTRAINT ' + QUOTENAME(fk.name + N'_CASCADEDELETES') 
       + N'    FOREIGN KEY (' 
            + STUFF((SELECT ',' + QUOTENAME(c.name)
                    FROM sys.columns AS c 
                    JOIN sys.foreign_key_columns AS fkc 
                        ON fkc.parent_column_id = c.column_id
                        AND fkc.parent_object_id = c.object_id
                    WHERE fkc.constraint_object_id = fk.object_id
                    ORDER BY fkc.constraint_column_id 
                    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
        + N') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
        + N'(' 
            + STUFF((SELECT ',' + QUOTENAME(c.name)
                     FROM sys.columns AS c 
                     JOIN sys.foreign_key_columns AS fkc 
                        ON fkc.referenced_column_id = c.column_id
                        AND fkc.referenced_object_id = c.[object_id]
                    WHERE fkc.constraint_object_id = fk.[object_id]
                    ORDER BY fkc.constraint_column_id 
                    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') 
        + N')'
        + N' ON DELETE CASCADE;'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs 
  ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

In our example database, we only have one FK, so the output produces a new FK for that one FK:

ALTER TABLE [dbo].[Orders] WITH NOCHECK
    ADD CONSTRAINT [FK_Orders_CustomerID_CASCADEDELETES] FOREIGN KEY ([CustomerID]) 
        REFERENCES [dbo].[Customers]([CustomerID]) 
        ON DELETE CASCADE;

After creating that FK, we can simply run our original delete again, to delete Grampa Joe from the Customers table, and it will cascade & delete the Orders as well:

DELETE c
FROM dbo.Customers AS c
WHERE CustomerID = 1;

SELECT *
FROM dbo.Orders

And we’re done! The last step is to simply drop the duplicate FKs we created. We can use another script to generate the DROP commands, just like we did with the creation. We can simply review & run the output from this script to drop FKs based on the appended _CASCADE DELETES we added to the name:

--adapted from https://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/
SELECT N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE fk.name LIKE '%CASCADEDELETES';

The benefit of this is that you don’t have to worry about getting the original FKs back to exactly how they were when you started, since you never have to touch them. This can be particularly beneficial if all your FKs are fully trusted, and you want to avoid potential blocking or runtime issues of recreating all the FKs WITH CHECK to ensure they are trusted at the end.

answered 2022-05-20 by Andy Mallon