target column(s) for foreign keys
    referencing its table.
   </para>
  </sect2>

  <sect2 id="ddl-constraints-fk">
   <title>Foreign Keys</title>

   <indexterm>
    <primary>foreign key</primary>
   </indexterm>

   <indexterm>
    <primary>constraint</primary>
    <secondary>foreign key</secondary>
   </indexterm>

   <indexterm>
    <primary>referential integrity</primary>
   </indexterm>

   <para>
    A foreign key constraint specifies that the values in a column (or
    a group of columns) must match the values appearing in some row
    of another table.
    We say this maintains the <firstterm>referential
    integrity</firstterm> between two related tables.
   </para>

   <para>
    Say you have the product table that we have used several times already:
<programlisting>
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
</programlisting>
    Let's also assume you have a table storing orders of those
    products.  We want to ensure that the orders table only contains
    orders of products that actually exist.  So we define a foreign
    key constraint in the orders table that references the products
    table:
<programlisting>
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer <emphasis>REFERENCES products (product_no)</emphasis>,
    quantity integer
);
</programlisting>
    Now it is impossible to create orders with non-NULL
    <structfield>product_no</structfield> entries that do not appear in the
    products table.
   </para>

   <para>
    We say that in this situation the orders table is the
    <firstterm>referencing</firstterm> table and the products table is
    the <firstterm>referenced</firstterm> table.  Similarly, there are
    referencing and referenced columns.
   </para>

   <para>
    You can also shorten the above command to:
<programlisting>
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer <emphasis>REFERENCES products</emphasis>,
    quantity integer
);
</programlisting>
    because in absence of a column list the primary key of the
    referenced table is used as the referenced column(s).
   </para>

   <para>
    You can assign your own name for a foreign key constraint,
    in the usual way.
   </para>

   <para>
    A foreign key can also constrain and reference a group of columns.
    As usual, it then needs to be written in table constraint form.
    Here is a contrived syntax example:
<programlisting>
CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  <emphasis>FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)</emphasis>
);
</programlisting>
    Of course, the number and type of the constrained columns need to
    match the number and type of the referenced columns.
   </para>

   <indexterm>
    <primary>foreign key</primary>
    <secondary>self-referential</secondary>
   </indexterm>

   <para>
    Sometimes it is useful for the <quote>other table</quote> of a
    foreign key constraint to be the same table; this is called
    a <firstterm>self-referential</firstterm> foreign key.  For
    example, if you want rows of a table to represent nodes of a tree
    structure, you could write
<programlisting>
CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text,
    ...
);
</programlisting>
    A top-level node would have NULL <structfield>parent_id</structfield>,
    while non-NULL <structfield>parent_id</structfield> entries would be
    constrained to reference valid rows of the table.
   </para>

   <para>
    A table can have more than one foreign key constraint.  This is
    used to implement many-to-many relationships between tables.  Say
    you have tables about products and orders, but now you want to
    allow one order to contain possibly many products (which the
    structure above did not allow).  You could use this table structure:
<programlisting>
CREATE TABLE products (
    product_no integer PRIMARY KEY,