Home Explore Blog CI



postgresql

23th chunk of `doc/src/sgml/ref/create_table.sgml`
6b0b059307eec693294faf9a5907b17d77a06ffe35d0aca50000000100000fa5
 be
          specified for <literal>ON DELETE</literal> actions.
         </para>

         <para>
          In a temporal foreign key, this option is not supported.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry id="sql-createtable-parms-references-refact-set-default">
        <term><literal>SET DEFAULT [ ( <replaceable>column_name</replaceable> [, ... ] ) ]</literal></term>
        <listitem>
         <para>
          Set all of the referencing columns, or a specified subset of the
          referencing columns, to their default values. A subset of columns
          can only be specified for <literal>ON DELETE</literal> actions.
          (There must be a row in the referenced table matching the default
          values, if they are not null, or the operation will fail.)
         </para>

         <para>
          In a temporal foreign key, this option is not supported.
         </para>
        </listitem>
       </varlistentry>
      </variablelist>
     </para>

     <para>
      If the referenced column(s) are changed frequently, it might be wise to
      add an index to the referencing column(s) so that referential actions
      associated with the foreign key constraint can be performed more
      efficiently.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-deferrable">
    <term><literal>DEFERRABLE</literal></term>
    <term><literal>NOT DEFERRABLE</literal></term>
    <listitem>
     <para>
      This controls whether the constraint can be deferred.  A
      constraint that is not deferrable will be checked immediately
      after every command.  Checking of constraints that are
      deferrable can be postponed until the end of the transaction
      (using the <link linkend="sql-set-constraints"><command>SET CONSTRAINTS</command></link> command).
      <literal>NOT DEFERRABLE</literal> is the default.
      Currently, only <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>,
      <literal>EXCLUDE</literal>, and
      <literal>REFERENCES</literal> (foreign key) constraints accept this
      clause.  <literal>NOT NULL</literal> and <literal>CHECK</literal> constraints are not
      deferrable.  Note that deferrable constraints cannot be used as
      conflict arbitrators in an <command>INSERT</command> statement that
      includes an <literal>ON CONFLICT DO UPDATE</literal> clause.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-initially">
    <term><literal>INITIALLY IMMEDIATE</literal></term>
    <term><literal>INITIALLY DEFERRED</literal></term>
    <listitem>
     <para>
      If a constraint is deferrable, this clause specifies the default
      time to check the constraint.  If the constraint is
      <literal>INITIALLY IMMEDIATE</literal>, it is checked after each
      statement. This is the default.  If the constraint is
      <literal>INITIALLY DEFERRED</literal>, it is checked only at the
      end of the transaction.  The constraint check time can be
      altered with the <link linkend="sql-set-constraints"><command>SET CONSTRAINTS</command></link> command.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-enforced">
    <term><literal>ENFORCED</literal></term>
    <term><literal>NOT ENFORCED</literal></term>
    <listitem>
     <para>
      When the constraint is <literal>ENFORCED</literal>, then the database
      system will ensure that the constraint is satisfied, by checking the
      constraint at appropriate times (after each statement or at the end of
      the transaction, as appropriate).  That is the default.  If the
      constraint is <literal>NOT ENFORCED</literal>, the database system will
      not check the constraint.  It is then up to the application code to
      ensure that the constraints are satisfied.  The database system might
      still assume that the data actually satisfies the constraint for
      optimization

Title: Foreign Key Optimization, Deferrable Constraints, and Enforced Constraints
Summary
This section discusses optimizing foreign key constraints by adding indexes to referencing columns. It also explains deferrable constraints, which can be checked at the end of a transaction using the SET CONSTRAINTS command, and their limitations with ON CONFLICT DO UPDATE clauses. It further elaborates on the INITIALLY IMMEDIATE and INITIALLY DEFERRED options for deferrable constraints and finally describes enforced and not enforced constraints.