| <!-- $PostgreSQL: pgsql/doc/src/sgml/ref/set_constraints.sgml,v 1.14 2006/09/16 00:30:20 momjian Exp $ --> |
| <refentry id="SQL-SET-CONSTRAINTS"> |
| <refmeta> |
| <refentrytitle id="SQL-SET-CONSTRAINTS-title">SET CONSTRAINTS</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>SET CONSTRAINTS</refname> |
| <refpurpose>set constraint checking modes for the current transaction</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-set-constraints"> |
| <primary>SET CONSTRAINTS</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| SET CONSTRAINTS { ALL | <replaceable class="parameter">name</replaceable> [, ...] } { DEFERRED | IMMEDIATE } |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>SET CONSTRAINTS</command> sets the behavior of constraint |
| checking within the current transaction. <literal>IMMEDIATE</literal> |
| constraints are checked at the end of each |
| statement. <literal>DEFERRED</literal> constraints are not checked until |
| transaction commit. Each constraint has its own |
| <literal>IMMEDIATE</literal> or <literal>DEFERRED</literal> mode. |
| </para> |
| |
| <para> |
| Upon creation, a constraint is given one of three |
| characteristics: <literal>DEFERRABLE INITIALLY DEFERRED</literal>, |
| <literal>DEFERRABLE INITIALLY IMMEDIATE</literal>, or |
| <literal>NOT DEFERRABLE</literal>. The third |
| class is always <literal>IMMEDIATE</literal> and is not affected by the |
| <command>SET CONSTRAINTS</command> command. The first two classes start |
| every transaction in the indicated mode, but their behavior can be changed |
| within a transaction by <command>SET CONSTRAINTS</command>. |
| </para> |
| |
| <para> |
| <command>SET CONSTRAINTS</command> with a list of constraint names changes |
| the mode of just those constraints (which must all be deferrable). The |
| current schema search path is used to find the first matching name if |
| no schema name is specified. <command>SET CONSTRAINTS ALL</command> |
| changes the mode of all deferrable constraints. |
| </para> |
| |
| <para> |
| When <command>SET CONSTRAINTS</command> changes the mode of a constraint |
| from <literal>DEFERRED</literal> |
| to <literal>IMMEDIATE</literal>, 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 <command>SET CONSTRAINTS</command> command. |
| If any such constraint is violated, the <command>SET CONSTRAINTS</command> |
| fails (and does not change the constraint mode). Thus, <command>SET |
| CONSTRAINTS</command> can be used to force checking of constraints to |
| occur at a specific point in a transaction. |
| </para> |
| |
| <para> |
| Currently, only foreign key constraints are affected by this |
| setting. Check and unique constraints are always effectively |
| not deferrable. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| This command only alters the behavior of constraints within the |
| current transaction. Thus, if you execute this command outside of a |
| transaction block |
| (<command>BEGIN</command>/<command>COMMIT</command> pair), it will |
| not appear to have any effect. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Compatibility</title> |
| |
| <para> |
| This command complies with the behavior defined in the SQL |
| standard, except for the limitation that, in |
| <productname>PostgreSQL</productname>, it only applies to |
| foreign-key constraints. |
| </para> |
| |
| </refsect1> |
| </refentry> |