How to Use Deferrable Constraint in PostgreSQL: Syntax and Examples

In PostgreSQL, you use DEFERRABLE constraints to delay the enforcement of foreign key or unique constraints until the end of a transaction. You define a constraint as DEFERRABLE INITIALLY DEFERRED or DEFERRABLE INITIALLY IMMEDIATE to control when the checks occur, allowing temporary violations inside transactions.

Syntax

The DEFERRABLE constraint allows you to specify when PostgreSQL should check the constraint during a transaction. You can use:

• DEFERRABLE INITIALLY IMMEDIATE: Checks the constraint immediately after each statement.
• DEFERRABLE INITIALLY DEFERRED: Checks the constraint only at the end of the transaction.
• NOT DEFERRABLE: The constraint is always checked immediately (default).

This is useful for foreign keys or unique constraints that might be temporarily violated during a transaction but must be valid at commit.

sql

CREATE TABLE child_table (
  id SERIAL PRIMARY KEY,
  parent_id INT,
  CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent_table(id)
    DEFERRABLE INITIALLY DEFERRED
);

Example

This example shows how to create two tables with a deferrable foreign key constraint and how to defer constraint checks inside a transaction.

sql

CREATE TABLE parent_table (
  id SERIAL PRIMARY KEY
);

CREATE TABLE child_table (
  id SERIAL PRIMARY KEY,
  parent_id INT,
  CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent_table(id)
    DEFERRABLE INITIALLY DEFERRED
);

BEGIN;
-- Insert child referencing a parent that does not exist yet
INSERT INTO child_table (parent_id) VALUES (1);
-- Insert the parent after
INSERT INTO parent_table DEFAULT VALUES;
COMMIT;

Output

BEGIN
INSERT 0 1
INSERT 0 1
COMMIT

Common Pitfalls

Common mistakes when using deferrable constraints include:

• Not declaring the constraint as DEFERRABLE, so you cannot defer it in transactions.
• Assuming constraints are deferred by default; they are NOT DEFERRABLE unless specified.
• Trying to defer constraints outside a transaction block; deferring only works inside BEGIN ... COMMIT.

Always check if your constraint is declared DEFERRABLE before using SET CONSTRAINTS commands.

sql

/* Wrong: constraint not deferrable, so this fails */
CREATE TABLE child_table (
  id SERIAL PRIMARY KEY,
  parent_id INT,
  CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent_table(id)
    NOT DEFERRABLE
);

BEGIN;
SET CONSTRAINTS fk_parent DEFERRED; -- ERROR: constraint is not deferrable
COMMIT;

Output

ERROR:  constraint "fk_parent" is not deferrable

Quick Reference

Keyword	Meaning
DEFERRABLE	Constraint can be deferred until transaction end
NOT DEFERRABLE	Constraint checked immediately after each statement
INITIALLY IMMEDIATE	Default check timing, constraint checked after each statement
INITIALLY DEFERRED	Constraint check delayed until transaction commit
SET CONSTRAINTS ... DEFERRED	Command to defer constraint checks inside a transaction
SET CONSTRAINTS ... IMMEDIATE	Command to enforce constraint checks immediately inside a transaction

Key Takeaways

Declare constraints as DEFERRABLE to allow deferring their checks inside transactions.

Use INITIALLY DEFERRED to delay constraint checks until transaction commit.

Deferring constraints only works inside explicit transaction blocks (BEGIN...COMMIT).

Attempting to defer NOT DEFERRABLE constraints causes errors.

Use SET CONSTRAINTS command to control constraint checking timing during transactions.