.\\" auto-generated by docbook2man-spec $Revision: 1.25 $
.TH "SET CONSTRAINTS" "7" "2002-11-22" "SQL - Language Statements" "SQL Commands"
.SH NAME
SET CONSTRAINTS \- set the constraint mode of the current transaction
.SH SYNOPSIS
.sp
.nf
SET CONSTRAINTS { ALL | \fIconstraint\fR [, ...] } { DEFERRED | IMMEDIATE }
  
.sp
.fi
.SH "DESCRIPTION"
.PP
\fBSET CONSTRAINTS\fR sets the behavior of constraint
evaluation in the current transaction. In \fBIMMEDIATE
\fRmode, constraints are checked at the end of each
statement. In \fBDEFERRED\fR mode, constraints are not
checked until transaction commit.
.sp
.RS
.B "Note:"
This command only alters the behavior of constraints within the
current transaction. Thus, if you execute this command outside
of an explicit transaction block (such as one started with
\fBBEGIN\fR), it will not appear to have any effect.
If you wish to change the behavior of a constraint without needing
to issue a \fBSET CONSTRAINTS\fR command in every
transaction, specify \fBINITIALLY DEFERRED\fR or
\fBINITIALLY IMMEDIATE\fR when you create the constraint.
.RE
.sp
.PP
When you change the mode of a constraint to be \fBIMMEDIATE
\fR, the new constraint mode takes effect retroactively:
any outstanding data modifications that would have been checked
at the end of the transaction (when using
\fBDEFERRED\fR) are instead checked during the
execution of the \fBSET CONSTRAINTS\fR command.
.PP
Upon creation, a constraint is always give one of three
characteristics: \fBINITIALLY DEFERRED\fR,
\fBINITIALLY IMMEDIATE DEFERRABLE\fR, or
\fBINITIALLY IMMEDIATE NOT DEFERRABLE\fR. The third
class is not affected by the \fBSET CONSTRAINTS\fR
command.
.PP
Currently, only foreign key constraints are affected by this
setting. Check and unique constraints are always effectively
initially immediate not deferrable.
.SH "COMPATIBILITY"
.SS "SQL92, SQL99"
.PP
\fBSET CONSTRAINTS\fR is defined in
SQL92 and SQL99. The
implementation in PostgreSQL complies
with the behavior defined in the standard, except for the
PostgreSQL limitation that \fBSET
CONSTRAINTS\fR cannot be applied to check or unique constraints.