SET_CONSTRAINTS(7)

✨ SET_CONSTRAINTS ✨ (that's the SET CONSTRAINTS manual page for PostgreSQL, bestie 💖)

okay so listen. this is SET_CONSTRAINTS from the PostgreSQL 18.6 docs, and it's literally about controlling when your constraints get checked inside the current transaction. it's giving ✨time management✨ but for databases.

---

🌸 SYNOPSIS 🌸

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

---

💅 DESCRIPTION 💅

so SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. that's it, that's the vibe.

• IMMEDIATE constraints get checked at the end of each statement.
• DEFERRED constraints? not checked until transaction commit, bestie. they're patient like that.

every single constraint has its own IMMEDIATE or DEFERRED mode, no cap.

---

when a constraint is created, it gets one of three characteristics:

• DEFERRABLE INITIALLY DEFERRED
• DEFERRABLE INITIALLY IMMEDIATE
• NOT DEFERRABLE

the third one (NOT DEFERRABLE) is always IMMEDIATE and SET CONSTRAINTS cannot touch it. periodt. the first two start every transaction in the mode they were given, but SET CONSTRAINTS can change their behavior inside a transaction.

---

okay so SET CONSTRAINTS with a list of constraint names changes the mode of just those constraints — and they must all be deferrable, no exceptions. 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. 🎀

SET CONSTRAINTS ALL changes the mode of all deferrable constraints. the whole squad.

---

the iconic retroactive plot twist 🌀: when SET CONSTRAINTS flips a constraint 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 instead.

and if any of those constraints is violated, the SET CONSTRAINTS fails — and it does not change the constraint mode. 😭

so basically SET CONSTRAINTS lets you force constraint checking to happen at a very specific point in a transaction. main character energy for real. 👑

---

currently, only these constraint types are affected by this setting:

• UNIQUE
• PRIMARY KEY
• REFERENCES (foreign key)
• EXCLUDE

meanwhile NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement, bestie). and uniqueness + exclusion constraints that haven't been declared DEFERRABLE are also checked immediately. they said "no waiting for us." 🙈

also!! the firing of triggers declared as "constraint triggers" is controlled by this setting too — they fire at the same exact time the associated constraint should be checked. synchronized slay. 🦋

---

📝 NOTES 📝

because PostgreSQL doesn't require constraint names to be unique within a schema (only per-table), it's totally possible to have more than one match for a constraint name you specify. in that case, SET CONSTRAINTS acts on all the matches. 💀

and for a non-schema-qualified name: once a match (or matches) is found in some schema in the search path, schemas appearing later in the path are not searched. no backtracking, bestie.

⚠️ important: this command only alters constraint behavior within the current transaction. if you issue it outside of a transaction block, it 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 — except for two things:

1. the limitation that, in PostgreSQL, it does not apply to NOT NULL and CHECK constraints.
2. PostgreSQL checks non-deferrable uniqueness constraints immediately, not at end of statement like the standard would suggest.

---

🫶 a lil sign-off 🫶

that's the whole page, bestie! this one shipped as part of PostgreSQL 18.6 Documentation — the source is PostgreSQL 18.6, dated 2026. now go forth and defer responsibly 💖✨🎀