with:
<programlisting>
CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);
</programlisting>
    and then insert the <literal>NOT</literal> key word where desired.
   </para>

   <tip>
    <para>
     In most database designs the majority of columns should be marked
     not null.
    </para>
   </tip>
  </sect2>

  <sect2 id="ddl-constraints-unique-constraints">
   <title>Unique Constraints</title>

   <indexterm>
    <primary>unique constraint</primary>
   </indexterm>

   <indexterm>
    <primary>constraint</primary>
    <secondary>unique</secondary>
   </indexterm>

   <para>
    Unique constraints ensure that the data contained in a column, or a
    group of columns, is unique among all the rows in the
    table.  The syntax is:
<programlisting>
CREATE TABLE products (
    product_no integer <emphasis>UNIQUE</emphasis>,
    name text,
    price numeric
);
</programlisting>
    when written as a column constraint, and:
<programlisting>
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    <emphasis>UNIQUE (product_no)</emphasis>
);
</programlisting>
    when written as a table constraint.
   </para>

   <para>
    To define a unique constraint for a group of columns, write it as a
    table constraint with the column names separated by commas:
<programlisting>
CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    <emphasis>UNIQUE (a, c)</emphasis>
);
</programlisting>
    This specifies that the combination of values in the indicated columns
    is unique across the whole table, though any one of the columns
    need not be (and ordinarily isn't) unique.
   </para>

   <para>
    You can assign your own name for a unique constraint, in the usual way:
<programlisting>
CREATE TABLE products (
    product_no integer <emphasis>CONSTRAINT must_be_different</emphasis> UNIQUE,
    name text,
    price numeric
);
</programlisting>
   </para>

   <para>
    Adding a unique constraint will automatically create a unique B-tree
    index on the column or group of columns listed in the constraint.
    A uniqueness restriction covering only some rows cannot be written as
    a unique constraint, but it is possible to enforce such a restriction by
    creating a unique <link linkend="indexes-partial">partial index</link>.
   </para>

   <indexterm>
    <primary>null value</primary>
    <secondary sortas="unique constraints">with unique constraints</secondary>
   </indexterm>

   <para>
    In general, a unique constraint is violated if there is more than
    one row in the table where the values of all of the
    columns included in the constraint are equal.
    By default, two null values are not considered equal in this
    comparison.  That means even in the presence of a
    unique constraint it is possible to store duplicate
    rows that contain a null value in at least one of the constrained
    columns.  This behavior can be changed by adding the clause <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>