SQL Foreign Key Cascades Explained

What and Why

A foreign key (FK) is a constraint that says “the value in this column must exist in some other table’s primary key.” It is how relational databases prevent orphan rows and dangling references. The cascade options tell the database what to do when the referenced row is deleted or updated.

Cascades are powerful and dangerous in equal measure. Used correctly they keep your data clean automatically; used carelessly they delete half your warehouse in one query.

Mental Model

A FK has two halves: the parent (referenced) and the child (referencing). When the parent row changes, the child must remain valid. Cascade actions describe how the database makes that happen: by propagating the change, blocking it, or nulling the link.

The five actions: CASCADE propagates, SET NULL clears the child’s reference, SET DEFAULT writes the default value, RESTRICT blocks immediately, NO ACTION blocks at end of statement (the default).

Hands-on Example

A blog schema:

CREATE TABLE authors (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE posts (
  id        serial PRIMARY KEY,
  author_id int REFERENCES authors(id) ON DELETE CASCADE,
  title     text NOT NULL
);

CREATE TABLE comments (
  id      serial PRIMARY KEY,
  post_id int REFERENCES posts(id) ON DELETE CASCADE,
  body    text NOT NULL
);

Now DELETE FROM authors WHERE id = 5 removes that author, all their posts, and all comments on those posts in one transaction.

Contrast with ON DELETE SET NULL:

ALTER TABLE posts
  DROP CONSTRAINT posts_author_id_fkey,
  ADD CONSTRAINT posts_author_id_fkey
    FOREIGN KEY (author_id) REFERENCES authors(id)
    ON DELETE SET NULL;

Deleting an author now leaves their posts in place with author_id = NULL (an “orphaned” but valid post). This is often what you want for audit logs or content that should outlive its creator.

Parent: authors(id=5)        Child: posts(author_id=5)

ON DELETE CASCADE     -> child row is DELETED
ON DELETE SET NULL    -> child.author_id = NULL
ON DELETE SET DEFAULT -> child.author_id = <default>
ON DELETE RESTRICT    -> parent DELETE FAILS now
ON DELETE NO ACTION   -> parent DELETE FAILS at
                        end of statement (default)

RESTRICT and NO ACTION look similar but differ in timing: RESTRICT fires immediately, NO ACTION defers to statement end, which lets you fix the violation inside the same statement (useful in complex CTEs).

Common Pitfalls

The most painful pitfall is unintended cascade depth. A delete on users cascades to accounts, then to transactions, then to audit_logs - and now a single bad cleanup script just nuked your compliance history. Always trace cascade chains before adding them.

Performance is another trap. Cascading deletes acquire row locks across every child table, in addition to scanning those tables to find matching rows. Without an index on the FK column, that scan is a full table scan. Always index FK columns - the database does not do it automatically (except in MySQL, which does).

A subtle one: ON DELETE CASCADE does not fire application triggers in some ORMs because the ORM does not know about the cascade - the database handled it silently. If you depend on Django signals or Rails callbacks for side effects (search reindex, cache invalidation), database cascades will bypass them.

Finally, SET NULL requires the child column to be nullable. If you also have NOT NULL on it, the constraint definition will be accepted but the cascade will fail at runtime.

Practical Tips

Default to ON DELETE RESTRICT for new FKs. It makes deletes explicit: the application must remove children first, which forces you to think about what should actually happen. Switch to CASCADE only for genuine ownership relationships (a comment truly belongs to its post).

Use SET NULL for “soft” references where the child should survive: posts written by a now-deleted author, orders placed by a now-deleted customer (for accounting).

Always index the child FK column. In Postgres, CREATE INDEX ON posts(author_id); is mandatory if you ever delete authors.

For ON UPDATE, cascades are rarely needed if your primary keys are immutable surrogate IDs (which they should be). Natural keys that can change are the main use case.

When designing schemas, draw the cascade graph on paper. If a single root delete can transitively wipe more than two tables, you probably want soft deletes (a deleted_at column) instead.

Wrap-up

Foreign key cascades automate referential integrity, but the automation cuts both ways. Pick the action per relationship based on what should happen to children when the parent disappears. Index FK columns, trace cascade chains, and prefer RESTRICT until you are sure CASCADE is what you want. That discipline keeps your data consistent without giving any single DELETE statement the power to ruin your week.