ON DELETE CASCADE vs SET NULL

Direct Answer

ON DELETE CASCADE automatically deletes child rows when the parent row is deleted. ON DELETE SET NULL sets the foreign key column to NULL instead, preserving the child row. Use CASCADE when the child has no independent meaning without the parent (e.g., order items without an order). Use SET NULL when the child can exist independently (e.g., an order after a customer is deleted).

Code Example

-- CASCADE: deleting an order automatically deletes its order_items
CREATE TABLE order_items (
    id       INT PRIMARY KEY,
    order_id INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- SET NULL: deleting an author keeps the posts but nulls their author_id
CREATE TABLE posts (
    id        INT PRIMARY KEY,
    author_id INT,                       -- must be nullable for SET NULL
    FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE SET NULL
);

Use CASCADE when children cannot exist without the parent; use SET NULL when the child should survive as an orphan.

Common Mistakes & Pitfalls

• Using CASCADE without tracing the graph: one parent delete can remove many owned rows through several foreign keys. Review retention and recovery first.
• Combining SET NULL with a required relationship: the referencing columns must accept NULL, and the application must define what the missing relationship means. A database may reject the DDL or the delete depending on the product and schema.
• Choosing actions table by table without a lifecycle rule: different relationships may correctly need different actions, but the reason should follow ownership, audit, and retention requirements.
• Treating soft delete as automatic protection: queries, unique constraints, cascades, and restoration still need an explicit design.