Home • Explore • Blog • CI
☰



postgresql
21th chunk of `doc/src/sgml/ref/create_table.sgml`
50e738b50426e1d30da01d219e8cfcd01ab16ff641c684630000000100000fa0
 <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 be marked <literal>PERIOD</literal>.  If the
      <replaceable class="parameter">refcolumn</replaceable> clause is omitted,
      and thus the reftable's primary key constraint chosen, the primary key
      must have its final column marked <literal>WITHOUT OVERLAPS</literal>.
     </para>

     <para>
      For each pair of referencing and referenced column, if they are of a
      collatable data type, then the collations must either be both
      deterministic or else both the same.  This ensures that both columns
      have a consistent notion of equality.
     </para>

     <para>
      The user
      must have <literal>REFERENCES</literal> permission on the referenced
      table (either the whole table, or the specific referenced columns).  The
      addition of a foreign key constraint requires a
      <literal>SHARE ROW EXCLUSIVE</literal> lock on the referenced table.
      Note that foreign key constraints cannot be defined between temporary
      tables and permanent tables.
     </para>

     <para>
      A value inserted into the referencing column(s) is matched against the
      values of the referenced table and referenced columns using the
      given match type.  There are three match types: <literal>MATCH
      FULL</literal>, <literal>MATCH PARTIAL</literal>, and <literal>MATCH
      SIMPLE</literal> (which is the default).  <literal>MATCH
      FULL</literal> will not allow one column of a multicolumn foreign key
      to be null unless all foreign key columns are null; if they are all
      null, the row is not required to have a match in the referenced table.
      <literal>MATCH SIMPLE</literal> allows any of the foreign key columns
      to be null; if any of them are null, the row is not required to have a
      match in the referenced table.
      <literal>MATCH PARTIAL</literal> is not yet implemented.
      (Of course, <literal>NOT NULL</literal> constraints can be applied to the
      referencing column(s) to prevent these cases from arising.)
     </para>

     <para>
      In addition, when the data in the referenced columns is changed,
      certain actions are performed on the data in this table's
      columns.  The <literal>ON DELETE</literal> clause specifies the
      action to perform when a referenced row in the referenced table is
      being deleted.  Likewise, the <literal>ON UPDATE</literal>
      clause specifies the action to perform when a referenced column
      in the referenced table is being updated to a new value. If the
      row is updated, but the referenced column is not actually
      changed, no action is done. Referential actions are executed as part of
      the data changing command, even if the constraint is deferred.  There
      are the following possible actions for each clause:

      <variablelist>
       <varlistentry id="sql-createtable-parms-references-refact-no-action">
        <term><literal>NO ACTION</literal></term>
        <listitem>
         <para>
Title: FOREIGN KEY Constraint Details: PERIOD, Collation, Permissions, and Referential Actions
Summary
This section elaborates on the usage of the PERIOD keyword, highlighting that a foreign key constraint is considered satisfied if referenced table records (based on non-PERIOD key parts) have PERIOD values that cover the referencing record's duration. Both referencing and referenced PERIOD columns must be of a range or multirange type. It also states the collation requirements between referenced and referencing columns, referencing table permissions for establishing foreign key constraints, and the lock requirements on the referenced table. The section defines the behavior of MATCH FULL and MATCH SIMPLE and introduces referential actions triggered by changes in the referenced table, like ON DELETE and ON UPDATE clauses. It then lists the available options for each clause, starting with NO ACTION.