SET CONSTRAINTS — set constraint checking modes for the current transaction
SET CONSTRAINTS { ALL | name
[, ...] } { DEFERRED | IMMEDIATE }
SET CONSTRAINTS
sets the behavior of constraint
checking within the current transaction. IMMEDIATE
constraints are checked at the end of each
statement. DEFERRED
constraints are not checked until
transaction commit. Each constraint has its own
IMMEDIATE
or DEFERRED
mode.
Upon creation, a constraint is given one of three
characteristics: 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
every transaction in the indicated mode, but their behavior can be changed
within a transaction by SET CONSTRAINTS
.
SET CONSTRAINTS
with a list of constraint names changes
the mode of just those constraints (which must all be deferrable). The
current schema search path is used to find the first matching name if
no schema name is specified. SET CONSTRAINTS ALL
changes the mode of all deferrable constraints.
When SET CONSTRAINTS
changes the mode of 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 are instead checked during the
execution of the SET CONSTRAINTS
command.
If any such constraint is violated, the SET CONSTRAINTS
fails (and does not change the constraint mode). Thus, SET
CONSTRAINTS
can be used to force checking of constraints to
occur at a specific point in a transaction.
Currently, only foreign key constraints are affected by this setting. Check and unique constraints are always effectively not deferrable.