| 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 — 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> |