Understanding Deferred Constraints

1. What deferred constraints are and why they exist

A deferred constraint is a constraint whose check does not happen immediately after each individual INSERT or UPDATE, but only at the end of the enclosing transaction, right before COMMIT. By default, a database checks every constraint immediately after each statement, a behavior SQL calls IMMEDIATE. For most rules this immediate behavior is correct and desirable, because an invalid intermediate row should be rejected as early as possible.

There are situations, however, where a row inevitably violates a constraint at an intermediate point within the same transaction, even though the final state after the transaction's last statement is fully valid. Two rows swapping their primary keys, or two tables with foreign keys referencing each other, are the classic examples. Without the ability to postpone the check to the end of the transaction, such inherently valid operations would be technically impossible to implement.

A deferred constraint solves exactly this problem by allowing the database to tolerate a temporary violation during the transaction, as long as the state is consistent at COMMIT. This flexibility comes with its own pitfalls, covered in detail in the following sections.

2. IMMEDIATE vs DEFERRED: the difference in detail

A constraint in IMMEDIATE mode is checked right after the statement that affects it. If a single INSERT violates the constraint, exactly that INSERT fails immediately, and the transaction can continue normally as long as the error is handled. This behavior is the default case for practically every constraint in most database systems and matches what most developers expect.

A constraint in DEFERRED mode, on the other hand, is only checked at the end of the transaction, right before COMMIT. Within the transaction, the constraint may therefore be temporarily violated without any statement failing. If the check fails at the end of the transaction, the entire transaction is rolled back, not just the single statement that caused the violation. This difference in the timing of error handling is the most important conceptual point to understand about deferred constraints.

3. DEFERRABLE INITIALLY DEFERRED vs INITIALLY IMMEDIATE

For a constraint to be deferrable at all, it must be explicitly marked as DEFERRABLE at definition time. A constraint without this keyword remains in IMMEDIATE mode forever and cannot be postponed at runtime. If a constraint is marked as DEFERRABLE, a second keyword determines the default behavior at the start of every transaction: INITIALLY IMMEDIATE checks by default right away, but can be switched to DEFERRED within the transaction. INITIALLY DEFERRED checks by default only at the end of the transaction, without any explicit switching needed.

The choice between these two options depends on the expected common case. A constraint that only rarely needs to be deferred, for example for a special primary key swap, should be DEFERRABLE INITIALLY IMMEDIATE so the normal, immediate behavior remains the default. A constraint where circular references within the same transaction are routinely built up benefits from INITIALLY DEFERRED as the default.

-- PostgreSQL: two ways to declare a deferrable foreign key
CREATE TABLE department (
    department_id BIGINT PRIMARY KEY,
    manager_id    BIGINT
);

CREATE TABLE employee (
    employee_id   BIGINT PRIMARY KEY,
    department_id BIGINT NOT NULL
);

-- DEFERRABLE, but checked immediately by default
ALTER TABLE employee
    ADD CONSTRAINT fk_employee_department
    FOREIGN KEY (department_id) REFERENCES department (department_id)
    DEFERRABLE INITIALLY IMMEDIATE;

-- DEFERRABLE, and deferred by default from the start of every transaction
ALTER TABLE department
    ADD CONSTRAINT fk_department_manager
    FOREIGN KEY (manager_id) REFERENCES employee (employee_id)
    DEFERRABLE INITIALLY DEFERRED;

4. SET CONSTRAINTS: switching timing at runtime

A constraint already defined as DEFERRABLE can be switched deliberately within a running transaction using the SET CONSTRAINTS command, independent of its INITIALLY default. SET CONSTRAINTS ALL DEFERRED postpones all deferrable constraints in the current transaction to the end of the transaction, and SET CONSTRAINTS constraint_name IMMEDIATE checks a single named constraint immediately, which also triggers an early check if a violation exists at that moment.

This targeted switching mechanism is especially useful for disabling a normally immediately checked constraint for exactly one problematic operation, without permanently changing the table's configuration. After explicitly switching to IMMEDIATE within the same transaction, the constraint automatically returns to its INITIALLY behavior defined in the table definition once a new transaction begins.

BEGIN;

-- Defer the specific constraint just for this transaction
SET CONSTRAINTS fk_employee_department DEFERRED;

-- This would normally violate the FK immediately, but checking is postponed
UPDATE employee SET department_id = 999 WHERE employee_id = 1;
INSERT INTO department (department_id, manager_id) VALUES (999, 1);

-- Force an immediate check right now, before COMMIT, to fail fast if needed
SET CONSTRAINTS fk_employee_department IMMEDIATE;

COMMIT;

5. Resolving circular foreign keys with deferred constraints

Two tables that reference each other via a foreign key, for example a department with a manager who is themselves an employee of that department, cannot be populated in a single transaction without deferred constraints. The first row in employee cannot be inserted before its department exists, but the department cannot be inserted with a valid manager_id before the employee exists. A classic chicken-and-egg problem that is unsolvable with IMMEDIATE constraints.

With a deferred constraint on the foreign key from department to employee, the ordering can be resolved: first insert the department with a temporarily NULL manager or with a value that becomes valid later, then insert the employee referencing the now-existing department, then update the department with the correct manager. Since the check happens only at the end of the transaction, it does not matter that an invalid state exists in the meantime.

BEGIN;

-- Insert the employee referencing a department that does not exist yet,
-- allowed only because the FK is deferred within this transaction
INSERT INTO employee (employee_id, department_id) VALUES (1, 10);

-- Insert the department referencing that same employee as manager
INSERT INTO department (department_id, manager_id) VALUES (10, 1);

-- Both foreign keys are satisfied at this point, COMMIT succeeds
COMMIT;

6. Primary key swaps without a unique violation

Another classic use case for deferred constraints is swapping two unique values, such as two sort positions in a ranking list. An UPDATE that sets position 1 to position 2 while simultaneously setting position 2 to position 1 inevitably briefly violates uniqueness under an IMMEDIATE UNIQUE constraint, because the first UPDATE statement momentarily creates two rows with the same position value before the second statement resolves the collision.

A deferred constraint on the UNIQUE column allows exactly this intermediate violation, as long as uniqueness holds again at the end of the transaction, after both UPDATE statements. Without a deferred constraint, the swap would have to go through a third, temporary intermediate value, which means additional statements and more complexity in application code.

CREATE TABLE ranking (
    item_id  BIGINT PRIMARY KEY,
    position INT NOT NULL,
    CONSTRAINT uq_ranking_position
        UNIQUE (position) DEFERRABLE INITIALLY DEFERRED
);

BEGIN;

-- Swap positions 1 and 2 directly, no temporary placeholder value needed
UPDATE ranking SET position = 2 WHERE item_id = 100; -- was position 1
UPDATE ranking SET position = 1 WHERE item_id = 200; -- was position 2

-- Uniqueness is momentarily violated between the two statements,
-- but restored before COMMIT, so the transaction succeeds
COMMIT;

7. Which constraint types can be deferred

Not every constraint type supports DEFERRABLE. In PostgreSQL, PRIMARY KEY, UNIQUE, FOREIGN KEY, and EXCLUDE constraints can be defined as deferrable, because their check is fundamentally based on an index that also cleanly supports a delayed check. CHECK and NOT NULL constraints, on the other hand, cannot be defined as deferrable in PostgreSQL, they are always checked immediately after each statement, because they refer only to the current row and a delay conceptually makes no sense for them.

This restriction is rarely a problem in practice, because the typical use cases for deferred constraints, circular references and value swaps, involve PRIMARY KEY, UNIQUE, or FOREIGN KEY anyway. Anyone wanting to formulate a rule where a CHECK-like condition should only be checked at the end of the transaction must fall back to a constraint trigger, a special trigger type in PostgreSQL that also supports DEFERRABLE.

8. Vendor differences: MySQL, PostgreSQL, Oracle

PostgreSQL and Oracle support real deferred constraints with the full SQL standard syntax DEFERRABLE INITIALLY DEFERRED and SET CONSTRAINTS. MySQL, on the other hand, does not know this concept at all: InnoDB always checks foreign keys immediately, there is no way to postpone the check of a single constraint to the end of the transaction. Instead, MySQL offers the global session variable FOREIGN_KEY_CHECKS, which disables foreign key checks entirely, independent of any single transaction.

The decisive difference: FOREIGN_KEY_CHECKS=0 disables checks completely and permanently until it is re-enabled, without an automatic check at the end of the transaction. Anyone using this switch must manually verify consistency afterward, while a real deferred constraint in PostgreSQL guarantees the check runs at COMMIT. When porting from PostgreSQL to MySQL, circular references and value-swap patterns therefore need a different architectural solution, usually through a temporary intermediate column or a changed order of write operations.

9. Debugging pitfalls and performance aspects

The biggest practical drawback of deferred constraints is that an error no longer occurs at the statement that actually caused it, but only at COMMIT, potentially many statements later. This temporal decoupling significantly complicates debugging, because the error message names the violated constraint but does not directly show which of the preceding statements was the actual cause. Detailed logging of all statements within a transaction helps reconstruct this connection after the fact when an error occurs.

On the performance side, a deferred constraint causes no fundamental overhead compared to an IMMEDIATE constraint, the check itself costs the same computation time, only the timing shifts. For very long transactions with many affected rows, however, the check bundled at the end of the transaction can noticeably affect COMMIT duration, because all postponed checks then occur at once instead of being spread over the transaction's runtime.

10. Summary

A deferred constraint moves the check of a PRIMARY KEY, UNIQUE, FOREIGN KEY, or EXCLUDE constraint from the individual statement to the end of the transaction. DEFERRABLE must be set explicitly at definition time, INITIALLY DEFERRED or INITIALLY IMMEDIATE determines the default behavior, and SET CONSTRAINTS allows targeted switching within a running transaction. Circular foreign keys between two tables and swapping unique values are the two classic use cases that would be unsolvable without this concept.

CHECK and NOT NULL constraints cannot be deferred, MySQL does not support this concept at all and only offers the global FOREIGN_KEY_CHECKS switch as a workaround. The most important practical drawback is complicated debugging, because an error only surfaces at COMMIT, not at the statement that actually caused it. Anyone aware of these tradeoffs can use deferred constraints deliberately for the few cases where they are truly indispensable.

11. FAQ: Understanding Deferred Constraints