SET_CONSTRAINTS(7pg16)

💅 SET_CONSTRAINTS 💅

hiii bestie 🫶 so this little page lives in the PostgreSQL 16.15 docs (manual section 7, if you're keeping track) and honestly it's one of those commands that seems lowkey boring until you realize it's literally the puppet master of when your constraints get checked. obsessed. let's get into it ✨

---

🌸 NAME 🌸

SET_CONSTRAINTS — sets the constraint check timing for the current transaction. that's it, that's the whole vibe 💅

---

📜 SYNOPSIS 📜

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

---

💖 DESCRIPTION 💖

okay so SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. breaking it down: IMMEDIATE constraints get checked at the end of each statement. DEFERRED constraints do not get checked until transaction commit. and every single constraint has its own IMMEDIATE or DEFERRED mode — no sharing, main character energy only 👑

when a constraint gets created, it's given one of three characteristics:

• DEFERRABLE INITIALLY DEFERRED
• DEFERRABLE INITIALLY IMMEDIATE
• NOT DEFERRABLE

the third class is always IMMEDIATE and honestly doesn't care about your SET CONSTRAINTS command at all — it's not affected, periodt. the first two classes start every transaction in the mode their name says, but SET CONSTRAINTS can change their behavior within a transaction. flexible queen behavior 🌈

SET CONSTRAINTS with a list of constraint names changes the mode of just those constraints — and heads up, they must all be deferrable. each constraint name can be schema-qualified. if you don't specify a schema name, the current schema search path is used to find the first matching name. meanwhile SET CONSTRAINTS ALL changes the mode of all deferrable constraints. all of them. every last one 💀

🚨 okay this part is the good part. when SET CONSTRAINTS changes a constraint's mode from DEFERRED to IMMEDIATE, the new mode takes effect retroactively. any outstanding data modifications that would have been checked at the end of the transaction get checked right then, during the execution of the SET CONSTRAINTS command. and if any such constraint is violated, the SET CONSTRAINTS fails — and does not change the constraint mode. so yeah, SET CONSTRAINTS can be used to force checking of constraints at a specific point in a transaction. it's giving time traveler 🕰️

currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE constraints are affected by this setting. NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). uniqueness and exclusion constraints that were never declared DEFERRABLE are also checked immediately. no deferring for them, sorry bestie 🙈

the firing of triggers declared as “constraint triggers” is also controlled by this setting — they fire at the same time that the associated constraint should be checked. synced up. coordinated. iconic 🎀

---

📝 NOTES 📝

so here's a little chaos for you: PostgreSQL does not require constraint names to be unique within a schema (only per-table), which means it's possible for more than one match to exist for a constraint name you specified. in that case, SET CONSTRAINTS acts on all matches. and for a non-schema-qualified name, once a match or matches have been found in some schema in the search path, schemas appearing later in the path are simply not searched. first come, first served 🍓

⚠️ also important: this command only alters the behavior of constraints within the current transaction. issuing this outside of a transaction block emits a warning and otherwise has no effect. so like... don't do that 💀

---

🦋 COMPATIBILITY 🦋

this command complies with the behavior defined in the SQL standard — with two exceptions, because PostgreSQL loves a plot twist:

• in PostgreSQL, it does not apply to NOT NULL and CHECK constraints
• PostgreSQL checks non-deferrable uniqueness constraints immediately, not at end of statement as the standard would suggest

---

okay that's the whole page, you ate that 🫶💖 this has been your girl dropping knowledge straight from the PostgreSQL 16.15 documentation, dated 2026 — go forth and defer responsibly ✨🎀👑