'\" t .\" Title: SET CONSTRAINTS .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets v1.75.2 .\" Date: 2016 .\" Manual: PostgreSQL 9.5.2 Documentation .\" Source: PostgreSQL 9.5.2 .\" Language: English .\" .TH "SET CONSTRAINTS" "5sql" "2016" "PostgreSQL 9.5.2" "PostgreSQL 9.5.2 Documentation" .\" ----------------------------------------------------------------- .\" * 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\&.