SQL Reference ยท Constraints and Data Types
SQL DEFERRABLE constraints
Reference for DEFERRABLE constraints: check foreign keys at COMMIT instead of per statement, INITIALLY DEFERRED vs IMMEDIATE, SET CONSTRAINTS and dialect support.
A deferrable constraint is one whose check can be postponed until the end of the transaction instead of after every statement. This lets you pass through states that are temporarily inconsistent, such as two rows that reference each other, as long as everything is consistent at COMMIT.
Constraints are checked immediately by default. Declaring a constraint DEFERRABLE allows it to be deferred, and INITIALLY DEFERRED makes deferral the starting behavior. The feature is part of the standard and is used mostly with foreign keys.
CREATE TABLE child ( id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES parent (id) DEFERRABLE INITIALLY DEFERRED );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
column_or_table_constraint [NOT] DEFERRABLE [INITIALLY DEFERRED | INITIALLY IMMEDIATE] SET CONSTRAINTS { ALL | constraint_name [, ...] } { DEFERRED | IMMEDIATE }; -- PostgreSQL, Oracle
| Part | Description |
|---|---|
NOT DEFERRABLE |
The default in the standard. The constraint is checked after every statement and cannot be postponed. |
DEFERRABLE |
The constraint may be deferred inside a transaction. |
INITIALLY IMMEDIATE |
Checked after each statement until a transaction asks to defer it. The default for a deferrable constraint. |
INITIALLY DEFERRED |
Checked at COMMIT, unless set to immediate. |
SET CONSTRAINTS |
Switches deferrable constraints for the rest of the current transaction. |
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | DEFERRABLE INITIALLY DEFERRED is supported on foreign keys. PRAGMA defer_foreign_keys = ON defers all foreign keys for the current transaction. No SET CONSTRAINTS. |
| PostgreSQL | Supported for UNIQUE, PRIMARY KEY, EXCLUDE and foreign keys. NOT NULL and CHECK are always immediate. |
| MySQL | Not supported. Disable checks temporarily with SET foreign_key_checks = 0, which is risky and applies to the session. |
| SQL Server | Not supported. Use ALTER TABLE ... NOCHECK CONSTRAINT, or order the statements correctly. |
| Oracle | Supported with SET CONSTRAINT(S) and ALTER SESSION SET CONSTRAINTS. Fully supported for foreign keys. |
-- PostgreSQL BEGIN; SET CONSTRAINTS ALL DEFERRED; UPDATE parent SET id = id + 1; UPDATE child SET parent_id = parent_id + 1; COMMIT; -- SQLite PRAGMA defer_foreign_keys = ON;
Usage notes
- Typical use: circular references (two tables that reference each other), swapping unique values between rows, and bulk loads where the parent order is awkward.
- Failure arrives late. With deferral, the error appears at
COMMITand the whole transaction is rolled back, so the message is far from the faulty statement. - Application code must be ready for
COMMITto fail, not just inserts. - Immediate-only constraints exist. In PostgreSQL, a unique index (as opposed to a unique constraint) cannot be deferred.
RESTRICTcannot be deferred. TheNO ACTIONaction can. See ON DELETE and ON UPDATE.- Do not use deferral to hide bad data. Order the statements properly when you can.
- Without a transaction every statement commits at once, so deferral changes nothing.