SET CONSTRAINTS
SQL - Language Statements
SET CONSTRAINTS
set the constraint mode of the current transaction
SET CONSTRAINTS
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }
Description
SET CONSTRAINTS sets the behavior of constraint
evaluation in the current transaction. In
IMMEDIATE mode, constraints are checked at the
end of each statement. In DEFERRED mode,
constraints are not checked until transaction commit.
When you change the mode of a constraint to be
IMMEDIATE, the new constraint mode takes effect
retroactively: any outstanding data modifications that would have
been checked at the end of the transaction (when using
DEFERRED) are instead checked during the
execution of the SET CONSTRAINTS command.
Upon creation, a constraint is always give one of three
characteristics: INITIALLY DEFERRED,
INITIALLY IMMEDIATE DEFERRABLE, or
INITIALLY IMMEDIATE NOT DEFERRABLE. The third
class is not affected by the SET CONSTRAINTS
command.
Currently, only foreign key constraints are affected by this
setting. Check and unique constraints are always effectively
initially immediate not deferrable.
Notes
This command only alters the behavior of constraints within the
current transaction. Thus, if you execute this command outside of a
transaction block
(BEGIN/COMMIT pair), it will
not appear to have any effect. If you wish to change the behavior
of a constraint without needing to issue a SET
CONSTRAINTS command in every transaction, specify
INITIALLY DEFERRED or INITIALLY
IMMEDIATE when you create the constraint.
Compatibility
This command complies with the behavior defined in the SQL
standard, except for the limitation that, in
PostgreSQL, it only applies to
foreign-key constraints.