Why should you care?

Deleting an order can auto-remove its line items — or you may want to block the delete. Cascade is a deliberate choice, not a default habit.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
IF OBJECT_ID(N'dbo.OrderItems', N'U') IS NOT NULL DROP TABLE dbo.OrderItems;
CREATE TABLE dbo.OrderItems (
    OrderItemId INT IDENTITY PRIMARY KEY,
    OrderId INT NOT NULL,
    ProductId INT NOT NULL,
    Qty INT NOT NULL CHECK (Qty > 0),
    CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (OrderId)
        REFERENCES dbo.Orders(OrderId) ON DELETE CASCADE
);
-- Demo: insert item, delete parent order, items go away
INSERT INTO dbo.OrderItems (OrderId, ProductId, Qty)
SELECT TOP (1) OrderId, 1, 2 FROM dbo.Orders;
-- DELETE FROM dbo.Orders WHERE OrderId = ...;  -- also deletes OrderItems

What happened?

• FK with ON DELETE CASCADE removes child OrderItems when the parent Order is deleted.
• Comment shows the dangerous power — use only when children have no independent meaning.

Practice next

1. Create OrderItems with CASCADE.
2. Insert a line for an existing order.
3. Delete that order inside a transaction, check items, then ROLLBACK.
4. Try ON UPDATE CASCADE on a rare mutable key.
5. Log deleted items with an INSTEAD OF trigger later.

Remember

CASCADE automates child cleanup. NO ACTION / RESTRICT blocks parent delete. Prefer explicit deletes in banking-style schemas.

Cart lines die with cart

DataVerse draft carts cascade-delete line items.

Outcome: Abandoned cart cleanup stays simple; paid orders use NO ACTION.