Two referential actions a foreign key can declare are RESTRICT and NO ACTION. Both reject an operation that would leave rows referencing a missing parent — so what is the practical difference between them?

middleshould knowfreq 40%

answer

Same refusal, different check time
RESTRICT = immediate, before triggers and other cascades
NO ACTION = end of statement; deferrable → COMMIT
RESTRICT cannot be deferred
MySQL/InnoDB treats them as equivalent

basics

~20 s

Timing. RESTRICT checks immediately, as part of the row operation, before other actions or triggers run. NO ACTION defers the check to the end of the statement — or to commit if the constraint is deferrable and deferred — so anything that removes or repoints the children first lets the operation succeed.

solid answer

~60 s

The outcome is the same when the children are genuinely still there: both reject. The difference is when the check runs.

RESTRICT is checked immediately, at the moment the parent row is deleted or updated, before any other referential action, trigger, or later row of the same statement has run. NO ACTION postpones the check to the end of the statement, and — if the constraint is declared deferrable and the transaction has set it deferred — all the way to commit.

That window matters. A single statement that deletes both parents and children, a cascade arriving from another foreign key, or an AFTER trigger that repoints the children can all satisfy the constraint before the NO ACTION check fires, so the operation succeeds. Under RESTRICT the same operation fails, because the check happened before any of that could take effect. Correspondingly, RESTRICT cannot be deferred to commit; deferral is only meaningful for NO ACTION.

Engines differ: PostgreSQL implements the distinction faithfully; MySQL/InnoDB treats both as an immediate check, so the two are effectively synonyms there.

The outcome is the same when the children are genuinely still there: both reject. The difference is **when the check runs**. `RESTRICT` is checked immediately, at the moment the parent row is deleted or updated, before any other referential action, trigger, or later row of the same statement has run. `NO ACTION` postpones the check to the end of the statement, and — if the constraint is declared deferrable and the transaction has set it deferred — all the way to commit. That window matters. A single statement that deletes both parents and children, a cascade arriving from another foreign key, or an AFTER trigger that repoints the children can all satisfy the constraint before the NO ACTION check fires, so the operation succeeds. Under RESTRICT the same operation fails, because the check happened before any of that could take effect. Correspondingly, RESTRICT cannot be deferred to commit; deferral is only meaningful for NO ACTION. Engines differ: PostgreSQL implements the distinction faithfully; MySQL/InnoDB treats both as an immediate check, so the two are effectively synonyms there.

code

sql · 7 lines

sql

-- children and parent removed in one statement
WITH gone AS (
  DELETE FROM category WHERE id = 7 RETURNING id
)
DELETE FROM article WHERE category_id IN (SELECT id FROM gone);
-- NO ACTION: checked at end of statement -> may succeed
-- RESTRICT:  checked as the category row is deleted -> fails

go deeper

for a junior

Knowing that both reject the operation and neither touches child rows is acceptable at this level.

for a middle

State the timing difference precisely — immediate versus end of statement, with deferral extending NO ACTION to commit — and give one scenario where it changes the outcome.

for a senior

Add trigger and cascade interactions, the debugging cost of commit-time errors, and the engine differences that make this non-portable.

for a principal

Turn it into a schema convention: which constraints may be deferred at all, what write patterns the codebase is allowed to rely on, and how that choice constrains bulk data operations.

Same verdict, different moment

Both actions are refusals: neither modifies child rows, and both raise a foreign key violation if, at the moment they look, children still reference a parent that has been deleted or re-keyed. The entire difference is when the engine looks.

RESTRICT — the check is part of the row operation itself. Before the parent delete or key update is applied, the engine looks for referencing children; if any exist, the statement fails immediately.
NO ACTION — the check is queued and evaluated at the end of the statement. If the constraint was declared DEFERRABLE and the transaction sets it deferred, it is evaluated even later, at COMMIT.

Why the window changes outcomes

Between the row operation and the end of the statement, several things can legitimately make the violation disappear:

The same statement removes the children. A delete driven by a join or a common table expression can remove parent and child rows in one statement. Row order within a statement is not something you control, so the child may be processed after the parent. Under NO ACTION the end-of-statement check sees a consistent state and passes. Under RESTRICT the parent's removal fails the instant it happens.
Another foreign key's CASCADE cleans up. If a second path cascades the delete to the children, that cascade runs during the statement. NO ACTION then finds nothing referencing the missing parent; RESTRICT already refused.
A trigger repoints the children. An AFTER trigger that reassigns children to a replacement parent satisfies the constraint before the NO ACTION check. RESTRICT never gives the trigger a chance, because it fires before AFTER-trigger effects are considered.
Deferral to commit. With a deferrable NO ACTION constraint set deferred, a transaction can delete the parent, do arbitrary intermediate work, and repoint the children later, so long as the state is consistent at COMMIT. RESTRICT cannot participate in that pattern at all — it is immediate by definition.

The intent behind each

RESTRICT expresses "this parent must not be deleted while it is referenced — full stop, and I want to know immediately." It is blunt, cheap, and it produces an error pointing at the exact statement, which makes it excellent for protecting entities that should never disappear implicitly: customers with invoices, accounts with ledger entries.

NO ACTION expresses "the invariant must hold when I next look", which is the more standard-flavoured, more permissive reading. It is what you want when transactions legitimately pass through intermediate states — bulk reorganisations, swapping a parent row for a replacement, migrations that renumber keys.

Diagnostics and error timing

The difference shows up in debugging. With RESTRICT, the error names the statement that caused it. With deferred NO ACTION, the error surfaces at COMMIT, far from the statement responsible, which is harder to attribute — a real operational cost of deferral, not just a theoretical one. End-of-statement NO ACTION sits in between: the failing statement is still identified.

Engine reality

The standard distinction is implemented faithfully by PostgreSQL, where RESTRICT is genuinely immediate and NO ACTION is checked at statement end (and can be deferred). MySQL/InnoDB checks both immediately and documents them as equivalent, and historically ignored deferrability entirely — so schemas that rely on the distinction are not portable. Oracle offers no RESTRICT keyword at all; the default no-action behaviour plus deferrable constraints covers the same ground. Always verify the semantics on the engine you deploy on rather than reasoning from the standard alone.

Practical guidance

If you simply want to protect a parent from deletion, either works; RESTRICT states the intent more clearly and fails earlier.
If your transactions need intermediate inconsistency — bulk moves, parent swaps, renumbering — you must use NO ACTION, and possibly a deferrable constraint, because RESTRICT forecloses those patterns.
Do not mix the two arbitrarily across a schema; pick a convention, because the difference is invisible in the data and only appears under unusual write patterns, which makes accidental inconsistency hard to notice.

## Same verdict, different moment Both actions are refusals: neither modifies child rows, and both raise a foreign key violation if, at the moment they look, children still reference a parent that has been deleted or re-keyed. The entire difference is **when the engine looks**. - **RESTRICT** — the check is part of the row operation itself. Before the parent delete or key update is applied, the engine looks for referencing children; if any exist, the statement fails immediately. - **NO ACTION** — the check is queued and evaluated at the **end of the statement**. If the constraint was declared `DEFERRABLE` and the transaction sets it deferred, it is evaluated even later, at **COMMIT**. ## Why the window changes outcomes Between the row operation and the end of the statement, several things can legitimately make the violation disappear: 1. **The same statement removes the children.** A delete driven by a join or a common table expression can remove parent and child rows in one statement. Row order within a statement is not something you control, so the child may be processed after the parent. Under NO ACTION the end-of-statement check sees a consistent state and passes. Under RESTRICT the parent's removal fails the instant it happens. 2. **Another foreign key's CASCADE cleans up.** If a second path cascades the delete to the children, that cascade runs during the statement. NO ACTION then finds nothing referencing the missing parent; RESTRICT already refused. 3. **A trigger repoints the children.** An AFTER trigger that reassigns children to a replacement parent satisfies the constraint before the NO ACTION check. RESTRICT never gives the trigger a chance, because it fires before AFTER-trigger effects are considered. 4. **Deferral to commit.** With a deferrable NO ACTION constraint set deferred, a transaction can delete the parent, do arbitrary intermediate work, and repoint the children later, so long as the state is consistent at COMMIT. RESTRICT cannot participate in that pattern at all — it is immediate by definition. ## The intent behind each RESTRICT expresses "this parent must not be deleted while it is referenced — full stop, and I want to know immediately." It is blunt, cheap, and it produces an error pointing at the exact statement, which makes it excellent for protecting entities that should never disappear implicitly: customers with invoices, accounts with ledger entries. NO ACTION expresses "the invariant must hold when I next look", which is the more standard-flavoured, more permissive reading. It is what you want when transactions legitimately pass through intermediate states — bulk reorganisations, swapping a parent row for a replacement, migrations that renumber keys. ## Diagnostics and error timing The difference shows up in debugging. With RESTRICT, the error names the statement that caused it. With deferred NO ACTION, the error surfaces at COMMIT, far from the statement responsible, which is harder to attribute — a real operational cost of deferral, not just a theoretical one. End-of-statement NO ACTION sits in between: the failing statement is still identified. ## Engine reality The standard distinction is implemented faithfully by PostgreSQL, where RESTRICT is genuinely immediate and NO ACTION is checked at statement end (and can be deferred). MySQL/InnoDB checks both immediately and documents them as equivalent, and historically ignored deferrability entirely — so schemas that rely on the distinction are not portable. Oracle offers no RESTRICT keyword at all; the default no-action behaviour plus deferrable constraints covers the same ground. Always verify the semantics on the engine you deploy on rather than reasoning from the standard alone. ## Practical guidance - If you simply want to protect a parent from deletion, either works; RESTRICT states the intent more clearly and fails earlier. - If your transactions need intermediate inconsistency — bulk moves, parent swaps, renumbering — you must use NO ACTION, and possibly a deferrable constraint, because RESTRICT forecloses those patterns. - Do not mix the two arbitrarily across a schema; pick a convention, because the difference is invisible in the data and only appears under unusual write patterns, which makes accidental inconsistency hard to notice.

Give a concrete case where NO ACTION succeeds but RESTRICT fails.
A single statement deletes a parent and its children together — for example a delete driven by a join across both tables. Row processing order within the statement is not guaranteed, so the parent may be removed before its children. NO ACTION checks at end of statement, by which time the children are gone and the state is consistent; RESTRICT fails at the instant the parent row is removed.
What is the operational downside of deferring the check to COMMIT?
The violation is reported at COMMIT rather than at the statement that caused it, so the error message points at the wrong place and the whole transaction rolls back. Debugging requires reconstructing which statement introduced the inconsistency. Deferral is worth it only when the transaction genuinely needs an intermediate inconsistent state.

saying these in an interview costs you the question

Claiming RESTRICT and NO ACTION differ in whether children are modified
Saying NO ACTION allows orphan rows to persist after commit
Believing RESTRICT can be deferred to commit time
Assuming the distinction behaves identically on every engine
Thinking one of them cascades the delete

Two referential actions a foreign key can declare are RESTRICT and NO ACTION. Both reject an operation that would leave rows referencing a missing parent — so what is the practical difference between them?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

Same verdict, different moment

Why the window changes outcomes

The intent behind each

Diagnostics and error timing

Engine reality

Practical guidance

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions4 questions

▾related topics4 topics

Same verdict, different moment

Why the window changes outcomes

The intent behind each

Diagnostics and error timing

Engine reality

Practical guidance

answer

go deeper

context