Index for Section CONSTRAINTS

Alphabetical listing for S

Bottom of page

SET
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 characteristics:
  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 every transaction 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
  specified. 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 retroactively: 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 command.
  If any such constraint is violated, the SET CONSTRAINTS 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.

Index for Section CONSTRAINTS

Alphabetical listing for S

Top of page