set_constraints(5)

NAME

       SET  CONSTRAINTS  -  set constraint checking modes 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 transaction commit. Each constraint
       has its own IMMEDIATE or DEFERRED mode.

       Upon creation, a constraint is given one of three  charac-
       teristics:  DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INI-
       TIALLY IMMEDIATE, or NOT DEFERRABLE. The  third  class  is
       always  IMMEDIATE  and  is  not  affected  by the SET CON-
       STRAINTS command. The first two classes start every trans-
       action  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 speci-
       fied.  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  retroac-
       tively: 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 com-
       mand.  If any such constraint is violated,  the  SET  CON-
       STRAINTS  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.

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.

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.

SQL - Language Statements   2008-01-03          SET CONSTRAINTS()