1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
|
'\" t
.\" Title: SET CONSTRAINTS
.\" Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\" Date: 2023
.\" Manual: PostgreSQL 15.5 Documentation
.\" Source: PostgreSQL 15.5
.\" Language: English
.\"
.TH "SET CONSTRAINTS" "7" "2023" "PostgreSQL 15.5" "PostgreSQL 15.5 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\&.
|
