SET CONSTRAINTS v16
SET CONSTRAINTS — Set constraint-checking modes for the current transaction.
SET CONSTRAINTS sets the behavior of constraint checking in the current transaction.
IMMEDIATE constraints are checked at the end of each statement.
DEFERRED constraints are checked when the transaction commits. Each constraint has its own
When you create a constraint, you give it one of three characteristics:
DEFERRABLE INITIALLY DEFERRED,
DEFERRABLE INITIALLY IMMEDIATE, or
NOT DEFERRABLE. The third class is always
IMMEDIATE and isn't affected by the
SET CONSTRAINTS command. The first two classes start every transaction in the indicated mode, but you can change their behavior in a transaction by using
SET CONSTRAINTS with a list of constraint names changes the mode of just those constraints. Those constraints must all be deferrable. If multiple constraints match any given name, all are affected.
SET CONSTRAINTS ALL changes the mode of all deferrable constraints.
SET CONSTRAINTS changes the mode of a constraint from
IMMEDIATE, the new mode takes effect retroactively. Any outstanding data modifications that normally are checked at the end of the transaction are instead checked while
SET CONSTRAINTS executes. If any such constraint is violated, the
SET CONSTRAINTS fails and doesn't change the constraint mode. Thus, you can use
SET CONSTRAINTS to force constraint checking to occur at a specific point in a transaction.
Currently, only foreign key constraints are affected by this setting. Check and unique constraints are never deferrable.
This command alters only the behavior of constraints in the current transaction. If you execute this command outside of a transaction block, it doesn't have any effect.