change is to drop the constraint
     (using <command>ALTER TABLE</command>), adjust the function definition,
     and re-add the constraint, thereby rechecking it against all table rows.
    </para>
   </note>
  </sect2>

  <sect2 id="ddl-constraints-not-null">
   <title>Not-Null Constraints</title>

   <indexterm>
    <primary>not-null constraint</primary>
   </indexterm>

   <indexterm>
    <primary>constraint</primary>
    <secondary>NOT NULL</secondary>
   </indexterm>

   <para>
    A not-null constraint simply specifies that a column must not
    assume the null value.  A syntax example:
<programlisting>
CREATE TABLE products (
    product_no integer <emphasis>NOT NULL</emphasis>,
    name text <emphasis>NOT NULL</emphasis>,
    price numeric
);
</programlisting>
    An explicit constraint name can also be specified, for example:
<programlisting>
CREATE TABLE products (
    product_no integer NOT NULL,
    name text <emphasis>CONSTRAINT products_name_not_null</emphasis> NOT NULL,
    price numeric
);
</programlisting>
   </para>

   <para>
    A not-null constraint is usually written as a column constraint.  The
    syntax for writing it as a table constraint is
<programlisting>
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    <emphasis>NOT NULL product_no</emphasis>,
    <emphasis>NOT NULL name</emphasis>
);
</programlisting>
    But this syntax is not standard and mainly intended for use by
    <application>pg_dump</application>.
   </para>

   <para>
    A not-null constraint is functionally equivalent to creating a check
    constraint <literal>CHECK (<replaceable>column_name</replaceable>
    IS NOT NULL)</literal>, but in
    <productname>PostgreSQL</productname> creating an explicit
    not-null constraint is more efficient.
   </para>

   <para>
    Of course, a column can have more than one constraint.  Just write
    the constraints one after another:
<programlisting>
CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price &gt; 0)
);
</programlisting>
    The order doesn't matter.  It does not necessarily determine in which
    order the constraints are checked.
   </para>

   <para>
    However, a column can have at most one explicit not-null constraint.
   </para>

   <para>
    The <literal>NOT NULL</literal> constraint has an inverse: the
    <literal>NULL</literal> constraint.  This does not mean that the
    column must be null, which would surely be useless.  Instead, this
    simply selects the default behavior that the column might be null.
    The <literal>NULL</literal> constraint is not present in the SQL
    standard and should not be used in portable applications.  (It was
    only added to <productname>PostgreSQL</productname> to be
    compatible with some other database systems.)  Some users, however,
    like it because it makes it easy to toggle the constraint in a
    script file.  For example, you could start 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>