Home • Explore • Blog • CI
☰



postgresql
20th chunk of `doc/src/sgml/ref/create_table.sgml`
38d6f5055ff382a3426ce0716941b9cf4bb3ac5e495259b90000000100000fa2
 Each <replaceable class="parameter">exclude_element</replaceable>
      defines a column of the index, so it can optionally specify a collation,
      an operator class, operator class parameters, and/or ordering options;
      these are described fully under <xref linkend="sql-createindex"/>.
     </para>

     <para>
      The access method must support <literal>amgettuple</literal> (see <xref
      linkend="indexam"/>); at present this means <acronym>GIN</acronym>
      cannot be used.  Although it's allowed, there is little point in using
      B-tree or hash indexes with an exclusion constraint, because this
      does nothing that an ordinary unique constraint doesn't do better.
      So in practice the access method will always be <acronym>GiST</acronym> or
      <acronym>SP-GiST</acronym>.
     </para>

     <para>
      The <replaceable class="parameter">predicate</replaceable> allows you to specify an
      exclusion constraint on a subset of the table; internally this creates a
      partial index. Note that parentheses are required around the predicate.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-references">
    <term><literal>REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ]</literal> (column constraint)</term>

   <term><literal>FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">column_name</replaceable> ] )
    REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">refcolumn</replaceable> ] ) ]
    [ MATCH <replaceable class="parameter">matchtype</replaceable> ]
    [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ]
    [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ]</literal>
    (table constraint)</term>

    <listitem>
     <para>
      These clauses specify a foreign key constraint, which requires
      that a group of one or more columns of the new table must only
      contain values that match values in the referenced
      column(s) of some row of the referenced table.  If the <replaceable
      class="parameter">refcolumn</replaceable> list is omitted, the
      primary key of the <replaceable class="parameter">reftable</replaceable>
      is used.  Otherwise, the <replaceable class="parameter">refcolumn</replaceable>
      list must refer to the columns of a non-deferrable unique or primary key
      constraint or be the columns of a non-partial unique index.
     </para>

     <para>
      If the last column is marked with <literal>PERIOD</literal>, it is
      treated in a special way.  While the non-<literal>PERIOD</literal>
      columns are compared for equality (and there must be at least one of
      them), the <literal>PERIOD</literal> column is not.  Instead, the
      constraint is considered satisfied if the referenced table has matching
      records (based on the non-<literal>PERIOD</literal> parts of the key)
      whose combined <literal>PERIOD</literal> values completely cover the
      referencing record's.  In other words, the reference must have a
      referent for its entire duration.  This column must be a range or
      multirange type.  In addition, the referenced table must have a primary
      key or unique constraint declared with <literal>WITHOUT
      OVERLAPS</literal>. Finally, if the foreign key has a PERIOD
      <replaceable class="parameter">column_name</replaceable> specification
      the corresponding <replaceable class="parameter">refcolumn</replaceable>,
      if present, must also
Title: CREATE TABLE Parameters: EXCLUDE Clause Details and FOREIGN KEY Constraint
Summary
This section expands on the EXCLUDE clause, explaining how each exclude_element defines a column of the index and can include collation, operator class, and ordering options. It clarifies that the access method for exclusion constraints must support amgettuple, excluding GIN, and that GiST or SP-GiST are typically used. A predicate can be used to specify an exclusion constraint on a table subset by creating a partial index. Then the section introduces the FOREIGN KEY constraint, outlining its function in ensuring that column values in the new table match values in the referenced columns of another table. If no refcolumn list is provided, the reftable's primary key is used. The refcolumn list must reference columns with a non-deferrable unique/primary key constraint or those of a non-partial unique index. It also introduces the PERIOD qualifier used in foreign keys on range or multirange types.