During my earlier years as a DBA, I was always warned away from DELETE CASCADE and had some of the pitfalls explained to me. But I’ve never actually examined it for myself. Having come across a database that uses it, I felt that I had to create a small test system, to understand it with some confidence
Test Environment
It doesn’t require anything complicated to study this feature, so I’ve gone for a somewhat basic structure.
A schema that shows orders and stock levels for products that are held in different warehouses. Pay attention to where the DELETE CASCADE clause appears.
Listing 1: Creating the test database
CREATE DATABASE CascadeTest;
GO
USE CascadeTest;
GO
CREATE TABLE Warehouse
(
WarehouseID INT IDENTITY(1, 1) PRIMARY KEY,
WarehouseName NVARCHAR(255) NOT NULL
);
GO
CREATE TABLE Customer
(
CustomerID INT IDENTITY(1, 1) PRIMARY KEY,
CustomerName NVARCHAR(255) NOT NULL
);
CREATE TABLE Products
(
ProductID INT IDENTITY(1, 1) PRIMARY KEY,
ProductDescription NVARCHAR(255) NOT NULL
);
CREATE TABLE StockLevels
(
StockLevelID INT IDENTITY(1, 1) PRIMARY KEY,
WarehouseID INT
FOREIGN KEY REFERENCES Warehouse (WarehouseID) ON DELETE CASCADE,
ProductID INT
FOREIGN KEY REFERENCES Products (ProductID) ON DELETE CASCADE,
Quantity INT NOT NULL
DEFAULT 0
);
CREATE TABLE Orders
(
OrderID INT IDENTITY(1, 1) PRIMARY KEY,
CustomerID INT NOT NULL
FOREIGN KEY REFERENCES dbo.Customer (CustomerID) ON DELETE CASCADE,
ProductID INT
FOREIGN KEY REFERENCES Products (ProductID) ON DELETE CASCADE,
Quantity INT NOT NULL,
WarehouseID INT NOT NULL
FOREIGN KEY REFERENCES Warehouse (WarehouseID) ON DELETE CASCADE,
OrderDate DATE NOT NULL,
OrderStatus NVARCHAR(50) CHECK (OrderStatus IN ( 'Pending', 'Shipped', 'Completed', 'Cancelled' )) NOT NULL
);
The DELETE CASCADE is specified against the foreign key. Basically, it is there as a check against the foreign key constraint, when an attempt is made to delete the associated rows in the ‘parent’ table, that the foreign key refers to.
The default is ON DELETE NOTHING – specifying that if the parent data is being deleted then the change will be abandoned. Hence the error that you may have seen elsewhere, where a row can’t be deleted because it is referred to in a foreign key constraint on another table.
ON DELETE NULL would set the column data to NULL, in the ‘child’ data, if the parent data is deleted.
ON DELETE CASCADE will delete the rows associated with the parent table that has now been deleted.
Now to create a small amount of test data.
Listing 2: Test data
INSERT INTO dbo.Warehouse
(
WarehouseName
)
VALUES
(N'Northern Warehouse'),
(N'Southern Warehouse'),
(N'Eastern Warehouse'),
(N'Western Warehouse');
GO
INSERT INTO dbo.Customer
(
CustomerName
)
VALUES
(N'Customer Alpha'),
(N'Customer Bravo'),
(N'Customer Charlie'),
(N'Customer Delta'),
(N'Customer Echo'),
(N'Customer Foxtrot');
GO
INSERT INTO dbo.Products
(
ProductDescription
)
VALUES
(N'White Widget'),
(N'Black Widget'),
(N'Yellow Widget'),
(N'Green Widget'),
(N'Blue Widget'),
(N'Purple Widget');
GO
INSERT INTO dbo.StockLevels
(
WarehouseID,
ProductID,
Quantity
)
VALUES
(1, 1, 10),
(1, 2, 30),
(1, 3, 9),
(2, 1, 25),
(2, 5, 50),
(2, 6, 100),
(3, 4, 10),
(3, 5, 1),
(4, 1, 50),
(4, 6, 5);
GO
INSERT INTO dbo.Orders
(
CustomerID,
ProductID,
Quantity,
WarehouseID,
OrderDate,
OrderStatus
)
VALUES
(1, 1, 5, 1, 'Jan 05 2024', N'PENDING'),
(1, 2, 3, 1, 'Jan 10 2024', N'PENDING'),
(2, 3, 5, 1, 'Feb 06 2024', N'CANCELLED'),
(3, 4, 2, 3, 'Mar 01 2024', N'SHIPPED'),
(3, 6, 1, 4, 'Apr 04 2024', N'COMPLETED'),
(4, 5, 2, 3, 'JUN 01 2024', N'SHIPPED'),
(5, 1, 3, 2, 'JUN 02 2024', N'SHIPPED'),
(6, 6, 5, 2, 'JUL 01 2024', N'PENDING');
Selecting all data from all of the tables, shows that we have 6 customers, 6 products in 4 warehouses, with 10 stock records and 8 orders.
Image 1: The initial data
Testing the Cascade Delete
Now, I delete one of the Customer rows and SSMS will inform me that one row has been deleted.
Image 2: Deleting one row
Now repeat the query from Image 1. This will show that there is now one less Customer row, as well as two rows less in the Orders table – as both rows within the Customer table had the CustomerID that was deleted from Customers.
Image 3: Checking the data after one row deleted
The two Orders rows were removed as a result of the DELETE CASCADE clause within the Orders table for the foreign key against the Customer table, and I had no control over that.
Generally, when deleting data, I use the OUTPUT clause to ensure I can reverse my actions – just in case. However, that wouldn’t be effective in this case, because the data was deleted from another table automatically. Additionally, looking at the schema of the Customers table, there is no indication that this cascade delete is in effect. You have to look at the Orders table to realise that this will happen.
Usually, when I change the data within a table I like to make sure that I’m causing the minimum amount of disruption possible. I need to consider how busy the server is and whether that table is heavily used at the time, to ensure any locks and suchlike are minimised. With this cascade option in use, the impact of the deletion has now spread throughout more of the database than I might have expected. Brent Ozar has an article about this at Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly – Brent Ozar Unlimited®
Conclusion
Personally, I think the advice I was given, to avoid DELETE CASCCADE, was sound advice. It isn’t immediately apparent what will happen, when data is modified and that clause is in use. It also doesn’t scale well. If I need to remove large amounts of data with the minimum amount of overhead I would always prefer to use something such as partition switching and truncation.
In the example I used in this article I would have identified the Customer row that needed to be removed and then removed it from the Orders table and then the Customer table, keeping all of the data aside for a limited period of time, in case it needed to be reversed.
References
https://sqlrambling.net/2018/02/21/automatic-data-purging-with-partition-truncation/
Adventures In Foreign Keys 3: Why Cascading Deletes Perform Slowly – Brent Ozar Unlimited®
https://sqlrambling.net/2023/07/20/the-output-clause-basic-examples/
Notes on SQL 