7-19. Deferred and nondeferred constraints

Constraints are checked at the time of:

• Statement execution, for nondeferred constraints
• COMMIT, for deferred constraints

So the order is (case: DML statement, followed by COMMIT):

1. Nondeferred constraints checked
2. COMMIT issued
3. Deferred constraints checked
4. COMMIT complete

Non-deferred constraints are enforced at the end of every DML statement and a violation causes the statement to roll back.

Deferred constraints are enforced when a transaction is committed and a violation causes the transaction to roll back.

A constraint that is defined as deferrable can be specified as one of the following:

• Initially immediable: specifies that by default it must function as an immediate constraint unless explicitly set otherwise using SET CONSTRAINT {constraint name, ... ALL} {IMMEDIATE DEFERRED}
• Initially deferred: specifies that by default the constraint must be enforced only at the end of the transaction.