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

   <para>
    It should be noted that a check constraint is satisfied if the
    check expression evaluates to true or the null value.  Since most
    expressions will evaluate to the null value if any operand is null,
    they will not prevent null values in the constrained columns.  To
    ensure that a column does not contain null values, the not-null
    constraint described in the next section can be used.
   </para>

   <note>
    <para>
     <productname>PostgreSQL</productname> does not support
     <literal>CHECK</literal> constraints that reference table data other than
     the new or updated row being checked.  While a <literal>CHECK</literal>
     constraint that violates this rule may appear to work in simple
     tests, it cannot guarantee that the database will not reach a state
     in which the constraint condition is false (due to subsequent changes
     of the other row(s) involved).  This would cause a database dump and
     restore to fail.  The restore could fail even when the complete
     database state is consistent with the constraint, due to rows not
     being loaded in an order that will satisfy the constraint.  If
     possible, use <literal>UNIQUE</literal>, <literal>EXCLUDE</literal>,
     or <literal>FOREIGN KEY</literal> constraints to express
     cross-row and cross-table restrictions.
    </para>

    <para>
     If what you desire is a one-time check against other rows at row
     insertion, rather than a continuously-maintained consistency
     guarantee, a custom <link linkend="triggers">trigger</link> can be used
     to implement that.  (This approach avoids the dump/restore problem because
     <application>pg_dump</application> does not reinstall triggers until after
     restoring data, so that the check will not be enforced during a
     dump/restore.)
    </para>
   </note>

   <note>
    <para>
     <productname>PostgreSQL</productname> assumes that
     <literal>CHECK</literal> constraints' conditions are immutable, that
     is, they will always give the same result for the same input row.
     This assumption is what justifies examining <literal>CHECK</literal>
     constraints only when rows are inserted or updated, and not at other
     times.  (The warning above about not referencing other table data is
     really a special case of this restriction.)
    </para>

    <para>
     An example of a common way to break this assumption is to reference a
     user-defined function in a <literal>CHECK</literal> expression, and
     then change the behavior of that
     function.  <productname>PostgreSQL</productname> does not disallow
     that, but it will not notice if there are rows in the table that now
     violate the <literal>CHECK</literal> constraint. That would cause a
     subsequent database dump and restore to fail.
     The recommended way to handle such a 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,