What are foreign key cascade options? | Interview Question

Q: What are foreign key cascade options?

**Target Audience:** Beginners who know what a foreign key is and want to know what happens when the parent row is deleted or changed, plus candidates who need the exact semantics of `NO ACTION` vs `RESTRICT`. **Difficulty:** Medium **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the page engine) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server facts are cited from their documentation. > **How to read this doc:** Each idea is first explained in plain language, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First A gym closes a member's account. What should happen to everything that points at that member? - Their locker assignment should simply go: a locker cannot belong to nobody. - Their past invoices must stay, because the accountant needs them. In

The problem

Question presented to candidate: "When you declare a foreign key you can add ON DELETE and ON UPDATE actions. What options exist, what does each one do to the child rows, and how do you pick one for a given relationship?"

What a strong answer should cover:

The five referential actions: NO ACTION (the default), RESTRICT, CASCADE, SET NULL, SET DEFAULT, each set separately for ON DELETE and ON UPDATE
NO ACTION vs RESTRICT: both refuse to orphan a child, but NO ACTION is checked at the end of the statement (or at commit when the constraint is deferred), while RESTRICT is checked immediately and cannot be deferred
Choosing by ownership: owned children (addresses, line items) cascade; records with their own value (orders, invoices) restrict; history that outlives the parent uses SET NULL or SET DEFAULT to a placeholder
Engine differences: SQLite enforces foreign keys only after PRAGMA foreign_keys = ON; MySQL InnoDB treats NO ACTION as RESTRICT and rejects SET DEFAULT; SQL Server has no RESTRICT keyword
Operational points: PostgreSQL does not index the referencing column automatically, so cascades and checks on large child tables need an index; cascades can delete far more than expected

Code / implementation expected: Yes — a schema with different actions per child table and a delete or update that shows each one.

Schema used by the examples:

foreign-key
cascade

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know what a foreign key is and want to know what happens when the parent row is deleted or changed, plus candidates who need the exact semantics of NO ACTION vs RESTRICT. Difficulty: Medium Engines: SQLite 3.45.0 (sql.js 1.10.2, the page engine) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server facts are cited from their documentation.

How to read this doc: Each idea is first explained in plain language, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

A gym closes a member's account. What should happen to everything that points at that member?

Their locker assignment should simply go: a loc

The solution is hidden so you can try first

15 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLDelete a customer: CASCADE removes addresses, SET NULL keeps tickets, SET DEFAULT reassigns reviews

SQLON UPDATE CASCADE: changing the parent key rewrites the child foreign keys

SQLSQLite trap: foreign keys are off by default, so a RESTRICT delete succeeds and leaves an orphan

PostgreSQL: NO ACTION can be deferred to the end of the transaction, RESTRICT cannot

CREATE TABLE plans_a (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE plans_b (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE subs_a (plan_id INTEGER REFERENCES plans_a(id)
  ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED);
CREATE TABLE subs_b (plan_id INTEGER REFERENCES plans_b(id)
  ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED);
INSERT INTO plans_a VALUES (1, 'basic');
INSERT INTO plans_b VALUES (1, 'basic');
INSERT INTO subs_a VALUES (1);
INSERT INTO subs_b VALUES (1);
CREATE TEMP TABLE outcome (fk_action TEXT, result TEXT);

-- Replace plan 1 inside one transaction: delete it, insert it again,
-- then force the deferred checks to run.
DO $$
BEGIN
  BEGIN
    DELETE FROM plans_a WHERE id = 1;
    INSERT INTO plans_a VALUES (1, 'basic v2');
    SET CONSTRAINTS ALL IMMEDIATE;
    INSERT INTO outcome VALUES ('NO ACTION', 'allowed');
  EXCEPTION WHEN foreign_key_violation THEN
    INSERT INTO outcome VALUES ('NO ACTION', 'rejected');
  END;
  BEGIN
    DELETE FROM plans_b WHERE id = 1;
    INSERT INTO plans_b VALUES (1, 'basic v2');
    SET CONSTRAINTS ALL IMMEDIATE;
    INSERT INTO outcome VALUES ('RESTRICT', 'allowed');
  EXCEPTION WHEN foreign_key_violation THEN
    INSERT INTO outcome VALUES ('RESTRICT', 'rejected');
  END;
END $$;

SELECT fk_action, result FROM outcome ORDER BY fk_action;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.