skip to content

When exactly does a relational engine check a constraint such as a UNIQUE or FOREIGN KEY rule — per row, at the end of the statement, or at COMMIT — and why does the answer matter?

level: middleimportance: should knowfreq 45%

answer

  1. Immediate = at latest end of statement
  2. Deferred = at COMMIT
  3. DEFERRABLE must be declared in DDL
  4. Circular FKs, key renumbering, bulk load
  5. Errors move to COMMIT — harder to debug

basics

~20 s

By default most constraints are checked immediately, at the end of each statement, so intermediate rows inside one statement do not trip them. Constraints declared DEFERRABLE INITIALLY DEFERRED are instead checked once at COMMIT, which lets a transaction pass through temporarily invalid states.

solid answer

~60 s

Default mode is **immediate**: the check happens no later than the end of the statement that caused it. That matters because a single statement can transiently create duplicates — `UPDATE seats SET seat_no = seat_no + 1` is legal under a UNIQUE constraint on an engine that evaluates uniqueness at statement end, and fails on one that checks strictly per row. The SQL standard also allows constraints to be **DEFERRABLE**, and set `INITIALLY DEFERRED` or switched at runtime with `SET CONSTRAINTS ... DEFERRED`. Deferred constraints are validated once, at COMMIT, so the transaction may be internally inconsistent in the middle and must be consistent at the end. The classic uses are circular foreign keys (A references B, B references A — neither row can be inserted first), and bulk loads or key renumbering that would otherwise need a careful ordering. The cost is that violations surface at COMMIT instead of at the offending statement, so error handling and debugging get harder, and the engine may hold more work until commit. Engine support varies — Oracle and PostgreSQL support deferral; MySQL/InnoDB does not.

code

sql · 22 lines
sql
CREATE TABLE departments (
  id         INT PRIMARY KEY,
  manager_id INT
);

CREATE TABLE employees (
  id      INT PRIMARY KEY,
  dept_id INT NOT NULL
);

ALTER TABLE departments
  ADD CONSTRAINT fk_dept_manager FOREIGN KEY (manager_id) REFERENCES employees(id)
  DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE employees
  ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(id)
  DEFERRABLE INITIALLY DEFERRED;

BEGIN;
  INSERT INTO departments (id, manager_id) VALUES (10, 100);
  INSERT INTO employees   (id, dept_id)    VALUES (100, 10);
COMMIT;  -- both foreign keys validated here

go deeper

for a junior

Know that constraints are normally checked right away, and that a violated constraint aborts the statement.

for a middle

Distinguish immediate (at latest end of statement) from deferred (at COMMIT), and name circular foreign keys and key renumbering as the reasons deferral exists.

for a senior

Discuss the operational cost — errors relocating to COMMIT, larger pending-check state, migration playbooks that opt in per transaction rather than per schema.

for a principal

Set the house rule: NOT DEFERRABLE by default, DEFERRABLE INITIALLY IMMEDIATE on the specific keys that migrations must bend, and an explicit contract that COMMIT can throw integrity errors.

## Three possible checking points 1. **Per row** — the check runs as each row is written. Simple, but it rejects statements that are only transiently invalid. 2. **End of statement (immediate)** — the SQL standard's default. The statement is applied, then the affected constraints are evaluated; if any fails, the statement's effects are undone. This is what lets `UPDATE t SET n = n + 1` succeed on a UNIQUE column even though row-by-row there are momentary duplicates. 3. **End of transaction (deferred)** — the check runs once, during COMMIT. The transaction may sit in a state that violates the rule for its entire body, as long as the rule holds at the end. In practice engines differ on whether a given constraint really behaves as (1) or (2). PostgreSQL evaluates unique indexes at row insertion for non-deferrable unique constraints, which is why the renumbering UPDATE can fail there unless the constraint is declared deferrable; Oracle behaves closer to the statement-end model. The safe portable statement in an interview is: 'immediate is the default and means at the latest at end of statement; whether a specific engine can absorb a within-statement duplicate depends on the engine, so I declare the constraint DEFERRABLE when I need that.' ## Deferrable constraints Standard syntax attaches the property to the constraint at DDL time: - `DEFERRABLE INITIALLY IMMEDIATE` — may be deferred, but behaves immediately unless a session asks otherwise. - `DEFERRABLE INITIALLY DEFERRED` — checked at COMMIT by default. - `NOT DEFERRABLE` — the default; can never be postponed. A session can then flip deferrable constraints for the current transaction with `SET CONSTRAINTS <name>|ALL DEFERRED`. A constraint that was not declared DEFERRABLE cannot be deferred at runtime — the DDL decision is load-bearing, and changing it later means an ALTER. ## Why you would defer - **Circular or mutually dependent foreign keys.** Table `departments.manager_id` references `employees`, and `employees.dept_id` references `departments`. With immediate FKs neither row can be inserted first. Deferred FKs let you insert both and validate at commit. - **Renumbering or swapping key values.** Swapping two rows' unique positions, or shifting an ordering column, passes through duplicate values. - **Bulk loads and migrations** where children arrive before parents and sorting the input is impractical. ## Why deferral is not the default and not free - **Errors move.** The violation is reported by COMMIT, not by the statement that caused it. You lose the natural pointer to the guilty statement, and application code that expected `INSERT` to throw now has to handle a failing commit. - **Failure is later and more expensive.** Work already done in the transaction is discarded at commit time rather than at the first bad row. - **Bookkeeping cost.** The engine has to remember which checks are pending, which can grow with the transaction's size. - **Weaker cross-session behaviour for uniqueness.** A deferred unique constraint still guards the final state, but conflicts between concurrent transactions are resolved later and can produce commit-time failures where an immediate constraint would have blocked or failed earlier. ## Constraints that are not deferrable NOT NULL and CHECK are typically evaluated as part of writing the row and cannot be deferred in common engines. So if you need a temporarily-invalid intermediate state guarded by a CHECK, deferral is not the tool — you restructure the statement, use a staging table, or relax the check. ## Practical guidance Keep constraints NOT DEFERRABLE by default so failures point at their cause. Declare specific foreign keys DEFERRABLE INITIALLY IMMEDIATE when you know a migration or a circular-reference path will need it, so a session can opt in without a DDL change under load. Reserve INITIALLY DEFERRED for constraints whose normal usage genuinely requires it, and be explicit in the application that COMMIT can throw an integrity error.

  • Can you defer a CHECK or NOT NULL constraint?
    In common engines, no. NOT NULL and CHECK are evaluated as the row is written and are not deferrable, so a transaction cannot pass through a state that violates them. If you need that, you restructure the work — stage rows in another table, or split the statement — rather than relying on deferral.
  • What is the downside of making everything DEFERRABLE INITIALLY DEFERRED so migrations never fight constraints?
    Every integrity error then surfaces from COMMIT instead of the statement that caused it, so you lose the direct pointer to the bad row and application error handling must cope with a failing commit. The engine also carries pending-check bookkeeping until commit, and long transactions do more wasted work before failing.

saying these in an interview costs you the question

  • Believing all constraints are checked only at COMMIT
  • Believing every constraint is checked strictly per row on every engine
  • Thinking SET CONSTRAINTS can defer a constraint that was declared NOT DEFERRABLE
  • Claiming deferral turns off the constraint rather than postponing the check
  • Assuming MySQL/InnoDB supports deferred constraints

context

resolving links…