Skip to content
Tutorials Center
My learning

Topics

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.

SQL
CREATE TABLE child (
  id INTEGER PRIMARY KEY,
  parent_id INTEGER REFERENCES parent (id) DEFERRABLE INITIALLY DEFERRED
);

Try it

SQL Insert the child before the parent inside one transaction

Press Run (or Ctrl + Enter) to see the result here.

SQL The check fails at COMMIT if it is still broken (fails on purpose at the end)

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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 COMMIT and the whole transaction is rolled back, so the message is far from the faulty statement.
  • Application code must be ready for COMMIT to fail, not just inserts.
  • Immediate-only constraints exist. In PostgreSQL, a unique index (as opposed to a unique constraint) cannot be deferred.
  • RESTRICT cannot be deferred. The NO ACTION action 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.