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,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);
</programlisting>
    Notice that the primary key overlaps with the foreign keys in
    the last table.
   </para>

   <indexterm>
    <primary>CASCADE</primary>
    <secondary>foreign key action</secondary>
   </indexterm>

   <indexterm>
    <primary>RESTRICT</primary>
    <secondary>foreign key action</secondary>
   </indexterm>

   <para>
    We know that the foreign keys disallow creation of orders that
    do not relate to any products.  But what if a product is removed
    after an order is created that references it?  SQL allows you to
    handle that as well.  Intuitively, we have a few options:
    <itemizedlist spacing="compact">
     <listitem><para>Disallow deleting a referenced product</para></listitem>
     <listitem><para>Delete the orders as well</para></listitem>
     <listitem><para>Something else?</para></listitem>
    </itemizedlist>
   </para>

   <para>
    To illustrate this, let's implement the following policy on the
    many-to-many relationship example above: when someone wants to
    remove a product that is still referenced by an order (via
    <literal>order_items</literal>), we disallow it.  If someone
    removes an order, the order items are removed as well:
<programlisting>
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products <emphasis>ON DELETE RESTRICT</emphasis>,
    order_id integer REFERENCES orders <emphasis>ON DELETE CASCADE</emphasis>,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);
</programlisting>
   </para>

   <para>
    The default <literal>ON DELETE</literal> action is <literal>ON DELETE NO
    ACTION</literal>; this does not need to be specified.  This means that the
    deletion in the referenced table is allowed to proceed.  But the
    foreign-key constraint is still required to be satisfied, so this
    operation will usually result in an error.  But checking of foreign-key
    constraints can also be deferred to later in the transaction (not covered
    in this chapter).  In that case, the <literal>NO ACTION</literal> setting
    would allow other commands to <quote>fix</quote> the situation before the
    constraint is checked, for example by inserting another suitable row into
    the referenced table or by deleting the now-dangling rows from the
    referencing table.
   </para>

   <para>
    <literal>RESTRICT</literal> is a stricter setting than <literal>NO
    ACTION</literal>.  It prevents deletion of a referenced row.
    <literal>RESTRICT</literal> does