<literal>NULLS
    NOT DISTINCT</literal>, like
<programlisting>
CREATE TABLE products (
    product_no integer UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis>,
    name text,
    price numeric
);
</programlisting>
    or
<programlisting>
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis> (product_no)
);
</programlisting>
    The default behavior can be specified explicitly using <literal>NULLS
    DISTINCT</literal>.  The default null treatment in unique constraints is
    implementation-defined according to the SQL standard, and other
    implementations have a different behavior.  So be careful when developing
    applications that are intended to be portable.
   </para>
  </sect2>

  <sect2 id="ddl-constraints-primary-keys">
   <title>Primary Keys</title>

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

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

   <para>
    A primary key constraint indicates that a column, or group of columns,
    can be used as a unique identifier for rows in the table.  This
    requires that the values be both unique and not null.  So, the following
    two table definitions accept the same data:
<programlisting>
CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);
</programlisting>

<programlisting>
CREATE TABLE products (
    product_no integer <emphasis>PRIMARY KEY</emphasis>,
    name text,
    price numeric
);
</programlisting>
   </para>

   <para>
    Primary keys can span more than one column; the syntax
    is similar to unique constraints:
<programlisting>
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    <emphasis>PRIMARY KEY (a, c)</emphasis>
);
</programlisting>
   </para>

   <para>
    Adding a primary key will automatically create a unique B-tree index
    on the column or group of columns listed in the primary key, and will
    force the column(s) to be marked <literal>NOT NULL</literal>.
   </para>

   <para>
    A table can have at most one primary key.  (There can be any number
    of unique constraints, which combined with not-null constraints are functionally almost the
    same thing, but only one can be identified as the primary key.)
    Relational database theory
    dictates that every table must have a primary key.  This rule is
    not enforced by <productname>PostgreSQL</productname>, but it is
    usually best to follow it.
   </para>

   <para>
    Primary keys are useful both for
    documentation purposes and for client applications.  For example,
    a GUI application that allows modifying row values probably needs
    to know the primary key of a table to be able to identify rows
    uniquely.  There are also various ways in which the database system
    makes use of a primary key if one has been declared; for example,
    the primary key defines the default 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