Deferred Constraint Concepts

The Default: Immediate Checking

As established in Topic 10.10, every constraint is checked immediately, at the moment each individual statement runs, by default. This is fine for the vast majority of applications, but it creates a real problem for certain multi-statement transactions that are only temporarily inconsistent partway through, even though they are perfectly consistent by the time the whole transaction commits.

The Classic Problem: Circular Foreign Keys

sql
CREATE TABLE employees (
    emp_id      INT PRIMARY KEY,
    dept_id     INT REFERENCES departments(dept_id)
);

CREATE TABLE departments (
    dept_id     INT PRIMARY KEY,
    manager_id  INT REFERENCES employees(emp_id)
);

employees references departments, and departments references employees — a genuine circular dependency. Inserting the very first department and its first employee is impossible under strict immediate checking: you can't insert the department without a manager who exists yet, and you can't insert that manager (an employee) without a department that exists yet. Something has to be created first, and whichever one goes first will momentarily violate its foreign key.

PostgreSQL's Solution: DEFERRABLE INITIALLY DEFERRED

sql
CREATE TABLE departments (
    dept_id     INT PRIMARY KEY,
    manager_id  INT REFERENCES employees(emp_id)
        DEFERRABLE INITIALLY DEFERRED
);

A constraint marked DEFERRABLE INITIALLY DEFERRED is not checked after each individual statement — it is checked only once, at COMMIT time, for the whole transaction. This lets you do:

sql
BEGIN;
INSERT INTO departments (dept_id, manager_id) VALUES (1, 101);  -- employee 101 doesn't exist yet!
INSERT INTO employees (emp_id, dept_id) VALUES (101, 1);        -- now it does
COMMIT;   -- constraint is checked NOW, and by this point everything is consistent

Without the deferred marking, the first INSERT would fail immediately, and this transaction would be impossible to express at all in a single pass.

The Two Deferrable Variants

sql
... DEFERRABLE INITIALLY IMMEDIATE   -- default check timing, but can be switched to deferred per-transaction with SET CONSTRAINTS
... DEFERRABLE INITIALLY DEFERRED    -- always deferred to COMMIT unless explicitly overridden

DEFERRABLE INITIALLY IMMEDIATE gives you the normal immediate-checking behavior by default, but still allows a specific transaction to opt into deferred checking on demand:

sql
SET CONSTRAINTS fk_departments_manager DEFERRED;

A plain constraint with neither DEFERRABLE keyword is NOT DEFERRABLE — it can never be relaxed, which is the default for constraints that don't specify otherwise.

Portability Note

Deferred constraint checking (DEFERRABLE INITIALLY DEFERRED, SET CONSTRAINTS) is a genuine PostgreSQL / Oracle / standard-SQL feature, but MySQL does not support deferrable constraints at all — the same circular-reference problem in MySQL is typically worked around by temporarily disabling FK checks (SET FOREIGN_KEY_CHECKS=0) for the duration of the batch, inserting all rows, then re-enabling — a much cruder tool than true per-transaction deferral.

Edge Cases

• Deferred constraints still fire — just later. If the data is genuinely inconsistent by COMMIT time, the entire transaction is rolled back, exactly as immediate checking would reject a single bad statement.
• Deferred checking applies to PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK in PostgreSQL, wherever declared deferrable — it is not exclusive to foreign keys, though FK circularity is the textbook motivating example.
• Overusing deferred constraints can hide bugs that immediate checking would have caught early in a long transaction, so it's applied selectively, not as a default posture.

Key Takeaways / Q&A

Q: What real-world scenario motivates DEFERRABLE INITIALLY DEFERRED? A: Circular foreign key references (or any multi-statement transaction temporarily inconsistent mid-way) that only become valid by the time the transaction commits.

Q: When is a DEFERRABLE INITIALLY DEFERRED constraint actually checked? A: At COMMIT time for the whole transaction, not after each individual statement.

Q: Does MySQL support DEFERRABLE constraints? A: No — MySQL has no deferred-constraint feature; the common workaround is temporarily disabling FK checks with SET FOREIGNKEYCHECKS=0.