What is a DEFERRABLE constraint in PostgreSQL and when is it needed?
A deferrable constraint can be checked at COMMIT instead of after each statement. This lets you insert rows that reference each other, or swap unique values, as long as everything is consistent by the end of the transaction.
Explain it simply
Two friends exchange seats in a cinema. For a moment, both seat numbers are held by the wrong people. The rule "one person per seat" is broken in the middle but fine at the end. Normally PostgreSQL checks the rule after each statement, so the swap fails. A deferrable constraint waits until COMMIT.
Only UNIQUE, PRIMARY KEY, EXCLUDE and FOREIGN KEY constraints can be deferrable; NOT NULL and CHECK cannot.
Example
CREATE TABLE seats (
id int PRIMARY KEY,
person text,
seat_no int,
CONSTRAINT uq_seat UNIQUE (seat_no) DEFERRABLE INITIALLY IMMEDIATE
);
BEGIN;
SET CONSTRAINTS uq_seat DEFERRED;
UPDATE seats SET seat_no = 2 WHERE person = 'Asha'; -- temporarily duplicates 2
UPDATE seats SET seat_no = 1 WHERE person = 'Rohit';
COMMIT; -- checked now: fineOther uses are inserting circular foreign keys (a user row pointing to their main address, which points back to the user) and bulk loads in any order. INITIALLY DEFERRED makes deferral the default.
A deferrable constraint costs a little more per row than an immediate one, so enable it only where needed.
What to say in the interview
"DEFERRABLE moves the check to commit time, which allows temporary violations such as swapping unique values or inserting mutually referencing rows. I enable it per transaction with SET CONSTRAINTS ... DEFERRED."
Common mistakes
- Trying to defer a CHECK or NOT NULL constraint, which is not allowed.