SET CONSTRAINTS

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.