What does it mean for a database constraint to be declared DEFERRABLE INITIALLY DEFERRED, and how does that change when the constraint is checked compared with the default behaviour?
answer
- Default = end of statement, deferred = at COMMIT
- DEFERRABLE = permission; INITIALLY = starting mode
- SET CONSTRAINTS only works on DEFERRABLE ones
- Cyclic FK insert, unique swap, bulk load
- Error lands on COMMIT, whole tx dies
basics
~20 sBy default a constraint is checked at the end of every statement. DEFERRABLE INITIALLY DEFERRED postpones checking to COMMIT, so data may be temporarily inconsistent inside the transaction. A violation then fails the COMMIT and aborts the whole transaction.
solid answer
~60 sSQL constraints have two independent settings: whether they *may* be postponed (DEFERRABLE / NOT DEFERRABLE) and when they are checked by default (INITIALLY IMMEDIATE / INITIALLY DEFERRED). - **NOT DEFERRABLE** (the default): the rule is verified at the end of each statement that touches the data. A violating statement fails; the transaction stays open. - **DEFERRABLE INITIALLY IMMEDIATE**: still checked per statement, but a transaction can switch it with `SET CONSTRAINTS ... DEFERRED`. - **DEFERRABLE INITIALLY DEFERRED**: checked once, at COMMIT. Deferring buys you a window inside the transaction where the data may legally violate the rule — that is what makes mutually-referencing inserts and unique-value swaps possible. The costs are real: the violation surfaces at COMMIT, far from the statement that caused it, and the whole transaction aborts. Other sessions never see the inconsistent state, because it is uncommitted. Engines differ on what may be deferred — PostgreSQL allows it for UNIQUE, PRIMARY KEY, EXCLUDE and foreign keys but not CHECK or NOT NULL; MySQL and SQL Server have no deferral at all.
code
sql · 9 linesALTER TABLE employee
ADD CONSTRAINT employee_dept_fk FOREIGN KEY (dept_id)
REFERENCES department (id)
DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE department
ADD CONSTRAINT department_mgr_fk FOREIGN KEY (manager_id)
REFERENCES employee (id)
DEFERRABLE INITIALLY DEFERRED;go deeper
Know that constraints are normally checked at the end of each statement and that DEFERRABLE INITIALLY DEFERRED moves the check to COMMIT.
Name all three declarations, explain SET CONSTRAINTS, and give the two motivating cases (cyclic foreign keys, swapping unique values).
Talk about the operational cost: commit-time errors with no statement attribution, the whole transaction aborting, the pending-check queue, and how your commit path must handle integrity errors.
Frame it as a schema policy — default NOT DEFERRABLE, deferral only where a modelled cycle demands it — and note the portability and feature restrictions (no deferral in MySQL/SQL Server, no upsert inference on deferrable unique constraints in PostgreSQL).
## What "checked immediately" actually means An integrity constraint is a rule the database itself enforces — a foreign key demanding a matching parent row, a unique constraint forbidding duplicate values, a CHECK expression that must be true. By default, SQL says such a rule is verified **at the end of every statement** that could break it. If the finished statement leaves the table violating the rule, the statement fails and its effects are undone; your transaction is still alive and you may retry or roll back. The guarantee that follows is stronger than "the database is consistent when I commit": it is "the database is consistent between every pair of statements". That is usually what you want, because it makes errors land on the exact statement that caused them. ## The three legal declarations SQL splits the behaviour into two orthogonal properties, written together in DDL: - **NOT DEFERRABLE** — the default. Always checked per statement, and no transaction can change that. Cheapest and clearest. - **DEFERRABLE INITIALLY IMMEDIATE** — checked per statement *by default*, but a transaction may opt into commit-time checking with `SET CONSTRAINTS <name> DEFERRED`. - **DEFERRABLE INITIALLY DEFERRED** — checked at COMMIT by default; a transaction may pull it back to per-statement checking with `SET CONSTRAINTS <name> IMMEDIATE`, which forces the pending checks to run right then. The `INITIALLY` word only sets the starting mode for each transaction; `DEFERRABLE` is the permission that makes runtime switching legal at all. `SET CONSTRAINTS` on a NOT DEFERRABLE constraint is an error (or silently ignored for the `ALL` form, depending on engine), which is a common source of "I deferred it and it still failed at the statement". ## What deferral is actually for Three recurring situations genuinely need it: 1. **Mutually-referencing rows.** Two tables whose foreign keys point at each other (an employee must belong to a department, a department must name a manager employee) have no valid insertion order — whichever row goes first references a row that does not exist yet. Deferring both foreign keys lets you insert the pair and satisfy the rules at COMMIT. 2. **Swapping or shifting unique values.** Exchanging two rows' `position` values under a unique constraint passes through a moment where both rows hold the same value. Deferring the unique check lets the transaction end in a valid state without the intermediate state being rejected. 3. **Order-independent bulk work.** Loading a graph of related rows, or a data migration that rewrites parents and children, becomes easier when you need not topologically sort the statements. ## What it costs - **Error attribution.** A deferred violation is raised by `COMMIT`, long after the statement that introduced it. You get the constraint name, not the guilty statement. Debugging a large transaction becomes archaeology. - **All-or-nothing failure.** With immediate checking, one bad statement fails and you can recover inside the transaction. With deferred checking, COMMIT fails and everything is gone. - **Bookkeeping.** Engines queue the pending checks — in PostgreSQL these are after-triggers held in the transaction's event queue — so a transaction touching millions of constrained rows accumulates per-row pending checks with memory (and spill) cost. - **Application code.** Your commit path must now handle constraint exceptions, not just the statement path. Many ORMs and frameworks flush and commit in places where you are not expecting an integrity error. - **Feature restrictions.** In PostgreSQL a *deferrable* unique constraint cannot be used for `ON CONFLICT` inference in an upsert — a nasty surprise if you make uniqueness deferrable schema-wide and then adopt upserts. ## Engine support (why portable schemas rarely rely on it) PostgreSQL supports DEFERRABLE on UNIQUE, PRIMARY KEY, EXCLUDE and REFERENCES (foreign key) constraints only; CHECK and NOT NULL are always immediate. Oracle supports DEFERRABLE broadly along with `SET CONSTRAINTS`. MySQL/InnoDB has no deferrable constraints at all — the nearest lever, disabling foreign-key checking for the session, *disables* rather than defers, so invalid data can actually be committed. SQL Server has no deferred checking either. A design that depends on deferral is therefore engine-bound; the portable alternatives are a nullable foreign key filled in by a follow-up UPDATE, or a temporary sentinel value during a swap. ## The practical rule Leave constraints NOT DEFERRABLE by default and make the specific one deferrable where a genuine cycle or swap requires it. Immediate checking is a diagnostic feature, not just a performance default: it tells you *which statement* broke the rule.
- Can you take an existing NOT DEFERRABLE constraint and defer it for one transaction?No. SET CONSTRAINTS only affects constraints that were declared DEFERRABLE; naming a non-deferrable one raises an error and the ALL form simply skips it. To change it you need DDL — in PostgreSQL, ALTER TABLE ... ALTER CONSTRAINT ... DEFERRABLE — which takes a strong table lock. That is why the deferability decision belongs in the schema, not in the request path.
- Which kinds of constraints can normally be deferred, and which cannot?In PostgreSQL, UNIQUE, PRIMARY KEY, EXCLUDE and foreign-key constraints can be deferrable; CHECK and NOT NULL cannot, because they are row-local and cheap to evaluate in place. The SQL standard is more permissive and Oracle allows deferral more broadly. MySQL and SQL Server offer no deferral of any kind.
- If a constraint is deferred, can another session read the temporarily invalid rows?No. The inconsistent rows exist only inside the uncommitted transaction, so under normal isolation other sessions never see them — they either see the pre-transaction state or, after a successful commit, a fully valid state. Deferral weakens the guarantee within a transaction, not the guarantee visible to concurrent readers.
Immediate checking is a proofreader stopping you at every sentence; deferred checking is one who only reads the finished page — faster to write a page that reorders itself midway, but when they reject it you lose the whole page and are not told which sentence was wrong.
saying these in an interview costs you the question
- Saying deferred means the constraint is optional or disabled — it is still enforced, just later
- Expecting the error on the offending statement rather than on COMMIT
- Assuming SET CONSTRAINTS works on any constraint, including NOT DEFERRABLE ones
- Claiming CHECK and NOT NULL can be deferred in PostgreSQL
- Thinking other sessions can observe the intermediate invalid state