Sets the constraint checking modes for the current transaction.
Syntax
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }Description
The SET CONSTRAINTS command sets the constraint check behavior in the current transaction. IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are checked only after the transaction is committed. Each constraint
has its own IMMEDIATE or DEFERRED mode.
When a constraint is created, one of the following three characteristics is assigned
to the constraint: DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT DEFERRABLE. The third class is always IMMEDIATE and is not affected by the SET CONSTRAINTS command. The first two classes start each transaction in the specified mode. You
can use the SET CONSTRAINTS command to change the behavior of the first two classes in a transaction.
If you specify a list of constraint names, the SET CONSTRAINTS command changes the modes of the specified constraints. The specified constraints
must be deferrable. If multiple constraints match a specified name, the modes of all
the matching constraints are changed. The SET CONSTRAINTS ALL command changes the modes of all deferrable constraints.
If the SET
CONSTRAINTS command changes the mode of a constraint from DEFERRED to IMMEDIATE, the new mode has a retroactive effect. During the execution of the SET
CONSTRAINTS command, all unfinished data changes are checked. These data changes are no longer
checked at the end of the transaction. If a constraint is violated, the SET CONSTRAINTS command fails and does not change the constraint mode. Therefore, the SET CONSTRAINTS command can be used to force constraints to be checked at a specific point in a transaction.
The setting of constraint checking modes affects only foreign key constraints. Check and UNIQUE constraints are not deferrable.