SET CONSTRAINTS

SET CONSTRAINTS — Set constraint checking timing for the current transaction

Synopsis

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

Description

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 the transaction commits. 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 category is always IMMEDIATE and is not affected by the SET CONSTRAINTS command. The first two categories start each transaction in the specified mode, but their behavior can be changed within a transaction using SET CONSTRAINTS.

SET CONSTRAINTS with a list of constraint names changes the mode of only those constraints (all of which must be deferrable).

Each constraint name can be schema-qualified. If no schema name is specified, the current schema search path is used to find the first matching name. 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 has retroactive effect: any outstanding data modifications (which would normally be checked at transaction end) are instead checked during execution of the SET CONSTRAINTS command. If any such constraint is violated, SET CONSTRAINTS will fail (and will not change the constraint mode). Thus, SET CONSTRAINTS can be used to force constraint checking at a specific point within a transaction.

Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE constraints are affected by this setting. NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at statement end). Unique and exclusion constraints that are not declared DEFERRABLE are also checked immediately.

The firing of triggers declared as "constraint triggers" is also controlled by this setting — they are fired at the same time as the related constraints are checked.

Notes

Because Halo does not require constraint names to be unique within a schema (but does require them to be unique within a table), there may be more than one constraint matching a specified constraint name. In this case, SET CONSTRAINTS will operate on all matches. For a non-schema-qualified name, once one or more matches are found in a schema in the search path, schemas later in the path will not be searched.

This command only modifies the behavior of constraints within the current transaction. Issuing this command outside a transaction block produces a warning and otherwise has no effect.