SQL DEFERRED

What is the SQL DEFERRED keyword?

Marks a constraint or transaction so that constraint checks are postponed until transaction commit.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DEFERRED:

SQL DEFERRED Full Explanation

DEFERRED is a transactional keyword used with constraints to delay their enforcement until the end of the current transaction. When a constraint (typically a FOREIGN KEY, UNIQUE, CHECK, or PRIMARY KEY) is declared DEFERRABLE and set to DEFERRED, the database allows temporary violations inside the transaction block. All deferred constraints are validated just before COMMIT. This behavior enables complex batch updates, circular foreign-key inserts, or bulk data loads that would otherwise fail on immediate constraint checks. DEFERRED can be applied in two ways: (1) at definition time with the clause DEFERRABLE INITIALLY DEFERRED, making the constraint start each transaction in the deferred state, or (2) at runtime with the statement SET CONSTRAINTS ... DEFERRED, switching one or more constraints to deferred mode for the current transaction. Some dialects such as SQLite also accept BEGIN DEFERRED TRANSACTION to start a lock-free transaction that behaves similarly. Non-DEFERRABLE constraints always fire immediately, and attempting to defer them raises an error. Because all deferred checks run at COMMIT, they can still block the commit if violations remain.

SQL DEFERRED Syntax

--Define a deferred foreign-key constraint
ALTER TABLE orders
  ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  DEFERRABLE INITIALLY DEFERRED;

--Defer all deferrable constraints for this transaction
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
--data modifications
COMMIT;

--SQLite: start a deferred transaction
BEGIN DEFERRED TRANSACTION;

SQL DEFERRED Parameters

Example Queries Using SQL DEFERRED

--1. Deferring specific constraint at runtime
BEGIN;
SET CONSTRAINTS fk_customer DEFERRED;
UPDATE orders SET customer_id = 999 WHERE id = 42;
INSERT INTO customers(id,name) VALUES (999,'Temp');
COMMIT;

--2. Circular insert with initially deferred constraints
BEGIN;
INSERT INTO employees(id,manager_id) VALUES (1,2);
INSERT INTO employees(id,manager_id) VALUES (2,1);
COMMIT;

Expected Output Using SQL DEFERRED

All statements within the transaction succeed even though they would temporarily break foreign-key rules
If the final state meets all constraints, COMMIT completes; otherwise it aborts with an error

Use Cases with SQL DEFERRED

Bulk importing data that refers to itself
Performing multi-step updates that temporarily violate foreign keys
Loading dimension tables before fact tables in data warehousing
Enabling circular references between rows during initial seeding

Common Mistakes with SQL DEFERRED

Declaring a constraint DEFERRABLE but forgetting to switch it to DEFERRED at runtime
Attempting to defer NONDEFERRABLE constraints, which raises an error
Assuming DEFERRED disables constraints permanently; they still fire at COMMIT
Using in MySQL, which does not support deferrable constraints

First Introduced In

PostgreSQL 7.3 (SQL standard: SQL-92 for DEFERRABLE)

Frequently Asked Questions

What is the difference between DEFERRABLE and DEFERRED?

DEFERRABLE marks a constraint as capable of being deferred. DEFERRED is the actual state that postpones its enforcement until commit.

Can I defer only specific constraints and not all of them?

Yes. Use SET CONSTRAINTS constraint_name DEFERRED inside a transaction to target individual constraints.

Will deferred constraints slow down my database?

Minimal overhead is added because checks are batched at commit. However, large transactions may delay error detection until the end, making debugging harder.

How do I find which constraints are deferrable?

Query the catalog view pg_constraint in PostgreSQL or user_constraints in Oracle, filtering on deferrable = 'YES'.