A foreign key can declare what should happen when the referenced parent row is deleted or when its key value is updated. What behaviours are available and what does each one do?
answer
- CASCADE / SET NULL / SET DEFAULT / RESTRICT / NO ACTION
- ON DELETE and ON UPDATE declared separately
- Default when omitted = NO ACTION
- SET NULL needs nullable cols; SET DEFAULT needs a sentinel parent
- Chains compose — blast radius lives in the schema
basics
~20 sFive: CASCADE deletes or rewrites the child rows, SET NULL blanks the child's key columns, SET DEFAULT sets them to the column default, and RESTRICT or NO ACTION reject the operation so orphans never appear. ON DELETE and ON UPDATE are declared separately; the default when unspecified is NO ACTION.
solid answer
~60 sA foreign key can attach an action to two events, declared separately: `ON DELETE` (the parent row is deleted) and `ON UPDATE` (the parent's referenced key value changes). - **CASCADE** — propagate. On delete, the child rows are deleted too. On update, the child's foreign key columns are rewritten to the new parent value. - **SET NULL** — the child survives with its foreign key columns set to NULL. Requires those columns to be nullable. - **SET DEFAULT** — the child's foreign key columns take their declared defaults; that default value must itself exist as a parent row, or the operation fails. - **RESTRICT** — reject the parent operation while children exist, checked immediately. - **NO ACTION** — also rejects, but the check is performed at the end of the statement (or at commit, if the constraint is deferred), so an operation that fixes the children within the same statement can succeed. Omitting the clause means NO ACTION. Choose per relationship: CASCADE for parts that cannot exist alone, SET NULL for optional links, RESTRICT for anything whose deletion should be a deliberate act.
code
sql · 15 linesCREATE TABLE order_line (
order_id BIGINT NOT NULL REFERENCES "order"(id) ON DELETE CASCADE,
line_no INT NOT NULL,
PRIMARY KEY (order_id, line_no)
);
CREATE TABLE article (
id BIGINT PRIMARY KEY,
category_id BIGINT NULL REFERENCES category(id) ON DELETE SET NULL
);
CREATE TABLE invoice (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customer(id) ON DELETE RESTRICT
);go deeper
List the five actions with a one-line meaning each, and note that ON DELETE and ON UPDATE are separate clauses defaulting to NO ACTION.
Add the prerequisites — nullable columns for SET NULL, an existing sentinel row for SET DEFAULT — and give a per-relationship decision rule.
Discuss composing actions along chains, deliberately mixing CASCADE and RESTRICT in one schema, and why cascades hide the true cost of a delete.
Frame it as a data-lifecycle policy: which entities may be erased implicitly, which require an explicit process, and how that interacts with retention, auditing, and downstream consumers.
## The problem the actions solve A foreign key says a child row's value must match an existing parent row. That invariant is threatened by two events: deleting the parent, and changing the parent's key value. A referential action is the schema's declaration of *how* the engine should preserve the invariant when that happens — either by changing the children or by refusing the operation. Actions are declared per event and per foreign key. `ON DELETE` and `ON UPDATE` are independent, so `ON DELETE CASCADE ON UPDATE RESTRICT` is entirely legal and often sensible. ## The five behaviours **CASCADE.** Propagates the parent operation to the children. On delete, matching child rows are deleted. On update, matching child rows have their foreign key columns rewritten to the parent's new value. It is the right choice when the child has no independent existence — order lines inside an order, address rows inside a customer, attributes owned by an entity. It is the wrong choice for anything that must survive its parent or has value on its own, such as ledger entries, invoices, or audit records. **SET NULL.** The child row stays; its foreign key columns become NULL. This encodes an *optional* relationship: an article whose category is removed becomes uncategorised. It requires the foreign key columns to be nullable, so it is incompatible with a NOT NULL column and with a foreign key that participates in the child's own primary key. **SET DEFAULT.** The child's foreign key columns take their column defaults. This only works if the default is itself a valid parent key — a sentinel row such as an "Unassigned" category must exist, or the operation fails with a foreign key violation, which is why this action is comparatively rare. **RESTRICT.** Refuses the parent delete or update while any child references it. The check happens immediately, as part of the row operation, before any other referential action or trigger has a chance to run. **NO ACTION.** Also refuses, but the check is deferred to the end of the statement — and to commit time if the constraint is declared deferrable and set deferred. Because the check comes later, a statement or transaction that removes or repoints the children in the meantime can succeed where RESTRICT would already have failed. When you write no clause at all, the standard default is NO ACTION. ## How they compose Actions chain. If deleting a customer cascades to orders, and orders cascade to order lines, one delete removes rows from three tables. Chains can be several levels deep and diamond-shaped when two paths lead to the same table, so the blast radius of a single statement is not visible at the call site — you have to read the schema. Mixing actions along a chain is common and intentional: cascade from order to order line, but restrict from customer to invoice so that a customer with financial history cannot be deleted at all. One cascade level does not continue through a SET NULL: once the child's reference is nulled, the child's own children still reference the child, which was not deleted, so nothing further happens. ## Choosing per relationship A useful decision rule: - Is the child a *part* of the parent, meaningless without it? → `ON DELETE CASCADE`. - Is the reference *optional* decoration? → `ON DELETE SET NULL`. - Does the child carry independent business or legal value, or is deleting the parent something that should never happen silently? → `RESTRICT` / `NO ACTION`, and let the application delete children deliberately. On the update side, `ON UPDATE CASCADE` only matters when the referenced key value can change — which, for a well-chosen immutable key, it cannot. Teams that keep their keys stable frequently declare `ON UPDATE RESTRICT` or simply leave the default, and the clause never fires. ## Practical notes - Actions are enforced by the engine on every delete path, including bulk deletes and ad-hoc ones, which is their main advantage over deleting children in application code. - They also make a delete far more expensive than it looks, because the engine must find and modify the children. - Not every action combination is supported everywhere: some engines restrict SET DEFAULT or self-referencing cascades, so verify against your target engine rather than assuming the standard.
- When is ON DELETE CASCADE the wrong choice?Whenever the child row has value independent of its parent — invoices, ledger entries, audit or event records, anything with retention or legal obligations. It is also risky where the child set is huge, because one small delete becomes a massive transaction. In those cases RESTRICT forces the deletion to be a deliberate, explicit operation.
- Why does ON UPDATE CASCADE rarely fire in a well-designed schema?It only does work when the referenced key value itself changes, and good practice is to make primary keys immutable and meaningless. If the key never changes, the clause never triggers. Needing it is usually a signal that a mutable business value was chosen as the key, and the better fix is a surrogate key plus a unique constraint on the business value.
saying these in an interview costs you the question
- Believing ON DELETE CASCADE is the safe default for every foreign key
- Thinking omitting the clause means the parent delete silently orphans the children
- Declaring SET NULL on a NOT NULL foreign key column and expecting it to work
- Assuming SET DEFAULT works without a matching parent row for the default value
- Thinking one delete only ever touches the immediate child table