Debian Codegraph @

doc/src/sgml/ref/set_constraints.sgml

raw
1 <!--
2 doc/src/sgml/ref/set_constraints.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-set-constraints">
7 <indexterm zone="sql-set-constraints">
8 <primary>SET CONSTRAINTS</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>SET CONSTRAINTS</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>SET CONSTRAINTS</refname>
19 <refpurpose>set constraint check timing for the current transaction</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 SET CONSTRAINTS { ALL | <replaceable class="parameter">name</replaceable> [, ...] } { DEFERRED | IMMEDIATE }
25 </synopsis>
26 </refsynopsisdiv>
27
28 <refsect1>
29 <title>Description</title>
30
31 <para>
32 <command>SET CONSTRAINTS</command> sets the behavior of constraint
33 checking within the current transaction. <literal>IMMEDIATE</literal>
34 constraints are checked at the end of each
35 statement. <literal>DEFERRED</literal> constraints are not checked until
36 transaction commit. Each constraint has its own
37 <literal>IMMEDIATE</literal> or <literal>DEFERRED</literal> mode.
38 </para>
39
40 <para>
41 Upon creation, a constraint is given one of three
42 characteristics: <literal>DEFERRABLE INITIALLY DEFERRED</literal>,
43 <literal>DEFERRABLE INITIALLY IMMEDIATE</literal>, or
44 <literal>NOT DEFERRABLE</literal>. The third
45 class is always <literal>IMMEDIATE</literal> and is not affected by the
46 <command>SET CONSTRAINTS</command> command. The first two classes start
47 every transaction in the indicated mode, but their behavior can be changed
48 within a transaction by <command>SET CONSTRAINTS</command>.
49 </para>
50
51 <para>
52 <command>SET CONSTRAINTS</command> with a list of constraint names changes
53 the mode of just those constraints (which must all be deferrable). Each
54 constraint name can be schema-qualified. The
55 current schema search path is used to find the first matching name if
56 no schema name is specified. <command>SET CONSTRAINTS ALL</command>
57 changes the mode of all deferrable constraints.
58 </para>
59
60 <para>
61 When <command>SET CONSTRAINTS</command> changes the mode of a constraint
62 from <literal>DEFERRED</literal>
63 to <literal>IMMEDIATE</literal>, the new mode takes effect
64 retroactively: any outstanding data modifications that would have
65 been checked at the end of the transaction are instead checked during the
66 execution of the <command>SET CONSTRAINTS</command> command.
67 If any such constraint is violated, the <command>SET CONSTRAINTS</command>
68 fails (and does not change the constraint mode). Thus, <command>SET
69 CONSTRAINTS</command> can be used to force checking of constraints to
70 occur at a specific point in a transaction.
71 </para>
72
73 <para>
74 Currently, only <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>,
75 <literal>REFERENCES</literal> (foreign key), and <literal>EXCLUDE</literal>
76 constraints are affected by this setting.
77 <literal>NOT NULL</literal> and <literal>CHECK</literal> constraints are
78 always checked immediately when a row is inserted or modified
79 (<emphasis>not</emphasis> at the end of the statement).
80 Uniqueness and exclusion constraints that have not been declared
81 <literal>DEFERRABLE</literal> are also checked immediately.
82 </para>
83
84 <para>
85 The firing of triggers that are declared as <quote>constraint triggers</quote>
86 is also controlled by this setting &mdash; they fire at the same time
87 that the associated constraint should be checked.
88 </para>
89 </refsect1>
90
91 <refsect1>
92 <title>Notes</title>
93
94 <para>
95 Because <productname>PostgreSQL</productname> does not require constraint
96 names to be unique within a schema (but only per-table), it is possible
97 that there is more than one match for a specified constraint name.
98 In this case <command>SET CONSTRAINTS</command> will act on all matches.
99 For a non-schema-qualified name, once a match or matches have been found in
100 some schema in the search path, schemas appearing later in the path are not
101 searched.
102 </para>
103
104 <para>
105 This command only alters the behavior of constraints within the
106 current transaction. Issuing this outside of a transaction block
107 emits a warning and otherwise has no effect.
108 </para>
109 </refsect1>
110
111 <refsect1>
112 <title>Compatibility</title>
113
114 <para>
115 This command complies with the behavior defined in the SQL
116 standard, except for the limitation that, in
117 <productname>PostgreSQL</productname>, it does not apply to
118 <literal>NOT NULL</literal> and <literal>CHECK</literal> constraints.
119 Also, <productname>PostgreSQL</productname> checks non-deferrable
120 uniqueness constraints immediately, not at end of statement as the
121 standard would suggest.
122 </para>
123
124 </refsect1>
125 </refentry>