'\" t
.\"     Title: SET CONSTRAINTS
.\"    Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\"      Date: 2021
.\"    Manual: PostgreSQL 13.4 Documentation
.\"    Source: PostgreSQL 13.4
.\"  Language: English
.\"
.TH "SET CONSTRAINTS" "7" "2021" "PostgreSQL 13.4" "PostgreSQL 13.4 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
SET_CONSTRAINTS \- set constraint check timing for the current transaction
.SH "SYNOPSIS"
.sp
.nf
SET CONSTRAINTS { ALL | \fIname\fR [, \&.\&.\&.] } { DEFERRED | IMMEDIATE }
.fi
.SH "DESCRIPTION"
.PP
\fBSET CONSTRAINTS\fR
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\&.
.PP
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
\fBSET CONSTRAINTS\fR
command\&. The first two classes start every transaction in the indicated mode, but their behavior can be changed within a transaction by
\fBSET CONSTRAINTS\fR\&.
.PP
\fBSET CONSTRAINTS\fR
with a list of constraint names changes the mode of just those constraints (which must all be deferrable)\&. Each constraint name can be schema\-qualified\&. The current schema search path is used to find the first matching name if no schema name is specified\&.
\fBSET CONSTRAINTS ALL\fR
changes the mode of all deferrable constraints\&.
.PP
When
\fBSET CONSTRAINTS\fR
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
\fBSET CONSTRAINTS\fR
command\&. If any such constraint is violated, the
\fBSET CONSTRAINTS\fR
fails (and does not change the constraint mode)\&. Thus,
\fBSET CONSTRAINTS\fR
can be used to force checking of constraints to occur at a specific point in a transaction\&.
.PP
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 (\fInot\fR
at the end of the statement)\&. Uniqueness and exclusion constraints that have not been declared
DEFERRABLE
are also checked immediately\&.
.PP
The firing of triggers that are declared as
\(lqconstraint triggers\(rq
is also controlled by this setting \(em they fire at the same time that the associated constraint should be checked\&.
.SH "NOTES"
.PP
Because
PostgreSQL
does not require constraint names to be unique within a schema (but only per\-table), it is possible that there is more than one match for a specified constraint name\&. In this case
\fBSET CONSTRAINTS\fR
will act on all matches\&. For a non\-schema\-qualified name, once a match or matches have been found in some schema in the search path, schemas appearing later in the path are not searched\&.
.PP
This command only alters the behavior of constraints within the current transaction\&. Issuing this outside of a transaction block emits a warning and otherwise has no effect\&.
.SH "COMPATIBILITY"
.PP
This command complies with the behavior defined in the SQL standard, except for the limitation that, in
PostgreSQL, it does not apply to
NOT NULL
and
CHECK
constraints\&. Also,
PostgreSQL
checks non\-deferrable uniqueness constraints immediately, not at end of statement as the standard would suggest\&.