SET CONSTRAINTS

Name

SET CONSTRAINTS  --  Sets the constraint mode for the current transaction block.

Synopsis

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

Parameters

ALL

Use this keyword to indicate that the mode you are specifying should be applied to all constraints within the current transaction.

constraint

Use this parameter to supply the name of a specific constraint you wish to set the mode of.

DEFERRED

Use this keyword to indicate that constraints (or a specific constraint) shouldn't be checked until the transaction reaches a COMMIT.

IMMEDIATE

Use this keyword to indicate that constraints (or a specific constraint) should be checked at the end of each statement within a transaction.

Results

SET CONSTRAINTS

This message is displayed if the constraint mode is changed successfully.

ERROR: Constraint 'someconstraint' does not exist

This error is displayed if you attempt to change the mode of a constraint that does not exist.

Description

Use the SET CONSTRAINTS command to set the constraint mode for all constraints within the current transaction block (or a single constraint, if specified). You can choose to set it to either IMMEDIATE mode or DEFERRED mode. IMMEDIATE mode will force the checking of all constraints at the end of each statement within the transaction. In DEFERRED mode, constraints are not checked until a COMMIT command is issued.

NoteNote
 

PostgreSQL 7.1.x (the most current version as of the writing of this book) only supports the use of these modes with foreign key constraints, as both check and unique constraints are set to a constraint mode that is not affected by this command.

Examples

The following example sets the constraint evaluation mode to IMMEDIATE for all constraints within the transaction.

booktown=# SET CONSTRAINTS ALL IMMEDIATE;
SET CONSTRAINTS