ON DELETE CASCADE in SQL Server: Powerful, and Genuinely Dangerous If Misused

Foreign keys (Fundamentals Chapter 5) can do more than just block invalid deletes — they can propagate changes automatically. That power cuts both ways, and this is one of the few topics in this course where the “safe default” genuinely differs from the option that looks most convenient in a demo.

The Four Options

CREATE TABLE dbo.Category2 (
    category_id INT IDENTITY(1,1) PRIMARY KEY,
    name        NVARCHAR(50) NOT NULL
);

CREATE TABLE dbo.Product2 (
    product_id  INT IDENTITY(1,1) PRIMARY KEY,
    category_id INT NOT NULL
        REFERENCES dbo.Category2(category_id) ON DELETE CASCADE ON UPDATE CASCADE,
    name        NVARCHAR(100) NOT NULL
);

The Real Risk

Seeing this concretely: if Category → Product is CASCADE, and Product → Review is also CASCADE, then one DELETE FROM Category2 WHERE category_id = 1 can silently remove every product in that category and every review those products ever received — all from a single statement, with no confirmation, no listing of what’s about to disappear. Multi-level CASCADE chains are exactly how a well-intentioned “clean up an old category” operation becomes an incident.

-- Prove the blast radius yourself before trusting a cascade chain in production:
SELECT COUNT(*) FROM dbo.Product2 WHERE category_id = 1; -- how many products?
-- If Review also cascades from Product, this number matters too:
-- SELECT COUNT(*) FROM dbo.Review WHERE product_id IN (SELECT product_id FROM dbo.Product2 WHERE category_id = 1);

A Concrete Decision Rule

CASCADE is right for genuinely dependent data — data with no meaning or value once its parent is gone (delete an Order, its OrderItems should go too; nobody wants orphaned line items pointing at a deleted order). It’s the wrong choice for anything with independent value — don’t cascade-delete Product just because a Category is removed; the products still exist and are still sellable, they just need a new category. Usually you want NO ACTION there, forcing an explicit, visible decision (reassign the products, or delete them deliberately) instead of an invisible side effect.

SQL Server also blocks a specific dangerous configuration outright: you cannot create multiple CASCADE paths that could reach the same table through different routes, since it can’t guarantee a deterministic order of operations — a useful, hardcoded safety net worth knowing about rather than fighting.