create_table.sgml

<REFENTRY ID="SQL-CREATETABLE">
 <REFMETA>
  <REFENTRYTITLE>
   CREATE TABLE
  </REFENTRYTITLE>
  <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
 </REFMETA>
 <REFNAMEDIV>
  <REFNAME>
   CREATE TABLE
  </REFNAME>
  <REFPURPOSE>
   Creates a new table
  </REFPURPOSE>
  
 <REFSYNOPSISDIV>
  <REFSYNOPSISDIVINFO>
   <DATE>1998-09-11</DATE>
  </REFSYNOPSISDIVINFO>
  <SYNOPSIS>
CREATE TABLE <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE> (
    <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> <REPLACEABLE CLASS="PARAMETER">type</REPLACEABLE>
    [ DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>]
    [, NOT NULL ] [ ,UNIQUE ]
    [<REPLACEABLE>column_constraint_clause</REPLACEABLE> | PRIMARY KEY } [ ... ] ]
    [, ... ]
    [, PRIMARY KEY ( <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> [, ...] ) ]
    [, CHECK ( <REPLACEABLE CLASS="PARAMETER">condition</REPLACEABLE> ) ]
    [, <REPLACEABLE>table_constraint_clause</REPLACEABLE> ]
    ) [ INHERITS ( <REPLACEABLE>inherited_table</REPLACEABLE> [, ...] ) ]
  </SYNOPSIS>
  
  <REFSECT2 ID="R2-SQL-CREATETABLE-1">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
    Inputs
   </TITLE>
   <PARA>
   
   <VARIABLELIST>
    <VARLISTENTRY>
     <TERM>
      <REPLACEABLE CLASS="PARAMETER">table</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       The name of a new table to be created.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
    <VARLISTENTRY>
     <TERM>
      <REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       The name of a column.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
    <VARLISTENTRY>
     <TERM>
      <REPLACEABLE CLASS="PARAMETER">type</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       The type of the column. This may include array specifiers.
       Refer to the <citetitle>PostgreSQL User's Guide</citetitle> for
       further information about data types and arrays.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
    <VARLISTENTRY>
     <TERM>
     DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       A default value for a column.
       See the DEFAULT clause for more information.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
    <VARLISTENTRY>
     <TERM>
      <REPLACEABLE>column_constraint_clause</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       The optional column constraint clauses specify a list of integrity 
       constraints or tests which new or updated entries must satisfy for
       an insert or update operation to succeed. Each constraint
       must evaluate to a boolean expression. Although <acronym>SQL92</acronym>
requires the <REPLACEABLE CLASS="PARAMETER">column_constraint_clause</REPLACEABLE>
 to refer to that column only, <ProductName>Postgres</ProductName>
 allows multiple columns
       to be referenced within a single column constraint.
       See the column constraint clause for more information.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>

    <VARLISTENTRY>
     <TERM>
      <REPLACEABLE>table_constraint_clause</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       The optional table CONSTRAINT clause specifies a list of integrity 
       constraints which new or updated entries must satisfy for
       an insert or update operation to succeed. Each constraint
       must evaluate to a boolean expression. Multiple columns
       may be referenced within a single constraint.
       Only one PRIMARY KEY clause may be specified for a table;
       PRIMARY KEY <REPLACEABLE>column</REPLACEABLE>
       (a table constraint) and PRIMARY KEY (a column constraint) are
       mutually exclusive..
       See the table constraint clause for more information.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>

    <VARLISTENTRY>
     <TERM>
     INHERITS <REPLACEABLE CLASS="PARAMETER">inherited_table</REPLACEABLE>
     </TERM>
     <LISTITEM>
      <PARA>
       The optional INHERITS clause specifies a collection of table
       names from which this table automatically inherits all fields.
       If any inherited field name appears more than once, 
<productname>Postgres</productname>
       reports an error.
 <productname>Postgres</productname> automatically allows the created
       table to inherit functions on tables above it in the inheritance
       hierarchy.
<note>
<title>Aside</title>
<para>
 Inheritance of functions is done according
       to the conventions of the Common Lisp Object System (CLOS).
</note>
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
   </VARIABLELIST>
   
   
  </REFSECT2>
  
  <REFSECT2 ID="R2-SQL-CREATETABLE-2">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
    Outputs
   </TITLE>
   <PARA>

       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>CREATE</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   Message returned if table is successfully created.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>ERROR</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   Message returned if table creation failed.
	   This is usually accompanied by some descriptive text, such as:
	   <ProgramListing>
amcreate: "<replaceable class="parameter">table</replaceable>" relation already exists
	   </ProgramListing>
	   which occurs at runtime, if the table specified already exists
	   in the database.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>

	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>ERROR:  DEFAULT: type mismatched</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   if data type of default value doesn't match the
	   column definition's data type.

   </VARIABLELIST>
  </REFSECT2>
 </REFSYNOPSISDIV>
 
 <REFSECT1 ID="R1-SQL-CREATETABLE-1">
  <REFSECT1INFO>
   <DATE>1998-09-11</DATE>
  </REFSECT1INFO>
  <TITLE>
   Description
  </TITLE>
  <PARA>
   <command>CREATE TABLE</command> will enter a new table into the current data
   base. The table will be "owned" by the user issuing the
   command.
   
  <PARA>
   The new table is created as a heap with no initial data.
   A table can have no more than 1600 columns (realistically,
   this is limited by the fact that tuple sizes must
   be less than 8192 bytes), but this limit may be configured
   lower at some sites. A table cannot have the same name as
   a system catalog table.
  </PARA>
  

  <REFSECT1 ID="R1-SQL-DEFAULTCLAUSE-1">
   <REFSECT1INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT1INFO>
   <TITLE>
    DEFAULT Clause
   </TITLE>
   <PARA>
    <SYNOPSIS>
DEFAULT <REPLACEABLE CLASS="PARAMETER">value</REPLACEABLE>
    </SYNOPSIS>

  <REFSECT2 ID="R2-SQL-DEFAULTCLAUSE-1">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
   Inputs
  </TITLE>
  <PARA>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	 <replaceable class="parameter">value</replaceable>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   The possible values for the default value expression are:
	   <itemizedlist>
	    <listitem>
	     <simpara>
	      a literal value
	     </simpara>
	    </listitem>
	    <listitem>
	     <simpara>
	      a user function
	     </simpara>
	    </listitem>
	    <listitem>
	     <simpara>
	      a niladic function
	     </simpara>
	    </listitem>
	   </itemizedlist>
	  </para>
	 </listitem>
	</VARLISTENTRY>
	
       </variablelist>
  <REFSECT2 ID="R2-SQL-DEFAULTCLAUSE-2">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
   Outputs
  </TITLE>
  <PARA>

  <REFSECT2 ID="R2-SQL-DEFAULTCLAUSE-3">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
   Description
  </TITLE>
  <PARA>
       The DEFAULT clause assigns a default data value to a column
       (via a column definition in the CREATE TABLE statement). 
       The data type of a default value must match the column definition's
       data type.
      </PARA>
      <PARA>
       An INSERT operation that includes a column without a specified
       default value will assign the NULL value to the column
       if no explicit data value is provided for it.
       Default <replaceable class="parameter">literal</replaceable> means
       that the default is the specified constant value.
       Default <replaceable class="parameter">niladic-function</replaceable>
       or <replaceable class="parameter">user-function</replaceable> means
       that the default
       is the value of the specified function at the time of the INSERT.
      </PARA>
      <PARA>
       There are two types of niladic functions:
<variablelist>
	<varlistentry>
	 <term>niladic USER</term>
	 <listitem>
	  <variablelist>
	   <varlistentry>
	    <term>CURRENT_USER / USER</term>
	    <listitem>
	     <simpara>See CURRENT_USER function</simpara>
	    </listitem>
	   </varlistentry>
	   <varlistentry>
	    <term>SESSION_USER</term>
	    <listitem>
	     <simpara>not yet supported</simpara>
	    </listitem>
	   </varlistentry>
	   <varlistentry>
	    <term>SYSTEM_USER</term>
	    <listitem>
	     <simpara>not yet supported</simpara>
	    </listitem>
	   </varlistentry>
	  </variablelist>
	 </listitem>
	</varlistentry>
	<varlistentry>
	 <term>niladic datetime</term>
	 <listitem>
	  <variablelist>
	   <varlistentry>
	    <term> CURRENT_DATE</term>
	    <listitem>
	     <simpara>See CURRENT_DATE function</simpara>
	    </listitem>
	   </varlistentry>
	   <varlistentry>
	    <term>CURRENT_TIME</term>
	    <listitem>
	     <simpara>See CURRENT_TIME function</simpara>
	    </listitem>
	   </varlistentry>
	   <varlistentry>
	    <term>CURRENT_TIMESTAMP</term>
	    <listitem>
	     <simpara>See CURRENT_TIMESTAMP function</simpara>
	    </listitem>
	   </varlistentry>
	  </variablelist>
	 </listitem>
	</varlistentry>
       </variablelist>

<para>
In the current release (v6.4), <productname>Postgres</productname>
evaluates all default expressions at the time the table is defined.
Hence, functions which are "non-cacheable" such as
 <function>CURRENT_TIMESTAMP</function> may not produce the desired
effect. For the particular case of date/time types, one can work
around this behavior by using 
<quote>
DEFAULT TEXT 'now'
</quote>
instead of
<quote>
DEFAULT 'now'
</quote>
or
<quote>
DEFAULT CURRENT_TIMESTAMP
</quote>.
This forces <productname>Postgres</productname> to consider the constant a string
type and then to convert the value to <type>timestamp</type> at runtime.

  <REFSECT2 ID="R2-SQL-DEFAULTCLAUSE-4">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
  Usage
   </TITLE>
   <PARA>
   To assign a constant value as the default for the
 columns <literal>did</literal> and <literal>number</literal>,
   and a string literal to the column <literal>did</literal>:
  
  <ProgramListing>
CREATE TABLE video_sales (
    did      VARCHAR(40) DEFAULT 'luso films',
    number   INTEGER DEFAULT 0,
    total    CASH DEFAULT '$0.0'
);
  </ProgramListing>

   <PARA>
   To assign an existing sequence
 as the default for the column <literal>did</literal>,
   and a literal to the column <literal>name</literal>:
  
  <ProgramListing>
CREATE TABLE distributors (
    did      DECIMAL(3)  DEFAULT NEXTVAL('serial'),
    name     VARCHAR(40) DEFAULT 'luso films'
);
  </ProgramListing>

  </REFSECT1>
  
  <REFSECT1 ID="R1-SQL-COLUMNCONSTRAINT-1">
   <REFSECT1INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT1INFO>
   <TITLE>
   Column CONSTRAINT Clause
   </TITLE>
   <para>
   <SYNOPSIS>
[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] { NOT NULL | UNIQUE | PRIMARY KEY | CHECK <replaceable class="parameter">constraint</replaceable> } [, ...]
   </SYNOPSIS>

  <REFSECT2 ID="R2-SQL-COLUMNCONSTRAINT-1">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
    Inputs
   </TITLE>
   <PARA>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	   <replaceable class="parameter">name</replaceable>
	 </TERM>
	 <LISTITEM>
	  <PARA>
          An arbitrary name given to the integrity constraint. 
If <replaceable class="parameter">name</replaceable> is not specified,
 it is generated from the table and column names,
which should ensure uniqueness for
 <replaceable class="parameter">name</replaceable>.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>

    <VARLISTENTRY>
     <TERM>
     NOT NULL
     </TERM>
     <LISTITEM>
      <PARA>
The column is not allowed to contain NULL values.
This is equivalent to the column constraint
 CHECK (<REPLACEABLE CLASS="PARAMETER">column</REPLACEABLE> NOT NULL).
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
    <VARLISTENTRY>
     <TERM>
     UNIQUE
     </TERM>
     <LISTITEM>
      <PARA>
The column must have unique values. In <ProductName>Postgres</ProductName>
this is enforced by an implicit creation of a unique index on the table.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>

    <VARLISTENTRY>
     <TERM>
     PRIMARY KEY
     </TERM>
     <LISTITEM>
      <PARA>
       This column is a primary key, which implies that uniqueness is
enforced by the system and that other tables may rely on this column
as a unique identifier for rows.
       See PRIMARY KEY for more information.
      </PARA>
     </LISTITEM>
    </VARLISTENTRY>
    
	<VARLISTENTRY>
	 <TERM>
	   <replaceable class="parameter">constraint</replaceable>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   The definition of the constraint.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
       </VARIABLELIST>

  <REFSECT2 ID="R2-SQL-COLUMNCONSTRAINT-2">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
    Description
   </TITLE>
   <para>
    A Constraint is a named rule: an SQL object which helps define
    valid sets of values by putting limits on the results of INSERT,
    UPDATE or DELETE operations performed on a Base Table. 
   </para>
   <para>
    There are two ways to define integrity constraints:
    table constraints, covered later, and column constraints, covered here.
   </para>
   <para>
    A column constraint is an integrity constraint defined as part
    of a column definition, and logically becomes a table
    constraint as soon as it is created. The column
    constraints available are:
    <simplelist columns="1">
     <member>PRIMARY KEY</member>
     <member>REFERENCES</member>
     <member>UNIQUE</member>
     <member>CHECK</member>
     <member>NOT NULL</member>
    </simplelist></para>
   <note>
    <para>
     <productname>Postgres</productname> does not yet 
(at release 6.4) support
     REFERENCES integrity constraints. The parser
accepts the REFERENCES syntax but ignores the clause.
    </para>
   </note>
   
  <REFSECT2 ID="R2-SQL-NOTNULL-1">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
    NOT NULL Constraint
   </TITLE>
   <SYNOPSIS>
[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] NOT NULL 
   </SYNOPSIS>
   <PARA>
    The NOT NULL constraint specifies a rule that a column may
    contain only non-null values. 
   </PARA>
   <PARA>
    The NOT NULL constraint is a column constraint only, and not allowed
as a table constraint.
   </PARA>

  <REFSECT3 ID="R3-SQL-NOTNULL-1">
   <REFSECT3INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT3INFO>
   <TITLE>
    Outputs
   </TITLE>
   <PARA>
   </PARA>
   <VARIABLELIST>
    <VARLISTENTRY>
     <TERM>
<replaceable>status</replaceable>
     </TERM>
     <LISTITEM>
      <PARA>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue>ERROR:  ExecAppend: Fail to add null value in not
	   null attribute "<replaceable class="parameter">column</replaceable>".</ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   This error occurs at runtime if one tries to insert a null value
	   into a column which has a NOT NULL constraint.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
       </variablelist>
     </LISTITEM>
    </VARLISTENTRY>
   </VARIABLELIST> 

  <REFSECT3 ID="R3-SQL-NOTNULL-2">
   <REFSECT3INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT3INFO>
   <TITLE>
Description
</title>
<para>

  <REFSECT3 ID="R3-SQL-NOTNULL-3">
   <REFSECT3INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT3INFO>
   <TITLE>
Usage
</title>

  <PARA>
   Define two NOT NULL column constraints on the table
 <classname>distributors</classname>,
one of which being a named constraint:
  </PARA>
  <ProgramListing>
CREATE TABLE distributors (
    did      DECIMAL(3) CONSTRAINT no_null NOT NULL,
    name     VARCHAR(40) NOT NULL
);
  </ProgramListing>

  <REFSECT2 ID="R2-SQL-UNIQUECLAUSE-1">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
    UNIQUE Constraint
   </TITLE>
   <synopsis>
[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE
   </SYNOPSIS>

   <refsect3>
    <title>Inputs</title>
<para>
    <variablelist>
     <varlistentry>
      <term>
      CONSTRAINT <replaceable class="parameter">name</replaceable>
      </term>
      <listitem>
       <para>
	An arbitrary label given to a constraint.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </refsect3>

   <refsect3>
    <title>Outputs</title>
    <PARA>
    <VARIABLELIST>
     <VARLISTENTRY>
      <TERM>
<replaceable>status</replaceable>
      </TERM>
      <LISTITEM>
       <PARA>
	<VARIABLELIST>
	 <VARLISTENTRY>
	  <TERM>
<returnvalue>ERROR: Cannot insert a duplicate key into a unique index.</returnvalue>
	  </term>
	  <listitem>
	   <para>
	    This error occurs at runtime if one tries to insert a
	    duplicate value into a column.
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist></para>
      </listitem>
     </varlistentry>
    </variablelist>
   </refsect3>

   <refsect3>
    <title>
Description
    </title>

    <PARA>
     The UNIQUE constraint specifies a rule that a group of one or
     more distinct columns of a table may contain only unique values.
    </para>
    <para>
     The column definitions of the specified columns do not have to
     include a NOT NULL constraint to be included in a UNIQUE
     constraint.  Having more than one null value in a column without a
     NOT NULL constraint, does not violate a UNIQUE constraint.
(This deviates from the <acronym>SQL92</acronym> definition, but
is a more sensible convention. See the section on compatibility
for more details.).
    </PARA>
    <PARA>
     Each UNIQUE column constraint must name a column that is
     different from the set of columns named by any other UNIQUE or
     PRIMARY KEY constraint defined for the table.
    </PARA>
    <Note>
     <Para>
      <productname>Postgres</productname> automatically creates a unique
 index for each UNIQUE constraint, to assure
      data integrity. See CREATE INDEX for more information.
     </Para>
    </Note>

  <REFSECT3 ID="R3-SQL-UNIQUECLAUSE-3">
   <TITLE>
Usage
</title>

  <PARA>
   Defines a UNIQUE column constraint for the table distributors.
   UNIQUE column constraints can only be defined on one column
   of the table:
  <ProgramListing>
   CREATE TABLE distributors (
        did      DECIMAL(3),
        name     VARCHAR(40) UNIQUE
        );
  </ProgramListing>
which is equivalent to the following specified as a table constraint:
  <ProgramListing>
CREATE TABLE distributors (
    did      DECIMAL(3),
    name     VARCHAR(40),
    UNIQUE(name)
);
  </ProgramListing>
  
  <REFSECT2 ID="R2-SQL-CHECK-1">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
<title>
The CHECK Constraint
</title>
  <SYNOPSIS>
[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] CHECK ( <replaceable>condition</replaceable> [, ...] ) 
  </SYNOPSIS>
   <refsect3 id="R3-SQL-CHECK-1">
    <title>Inputs</title>
   <PARA>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	  <ReturnValue><replaceable class="parameter">name</replaceable></ReturnValue>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   An arbitrary name given to a constraint.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	<VARLISTENTRY>
	 <TERM>
	  <replaceable>condition</replaceable>
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   Any valid conditional expression evaluating to a boolean result.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
       </variablelist>
  </REFSECT3>

  <REFSECT3 ID="R3-SQL-CHECK-2">
   <REFSECT3INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT3INFO>
    <TITLE>
     Outputs
    </TITLE>
    <PARA>
     <VARIABLELIST>
      <VARLISTENTRY>
       <TERM>
<replaceable>status</replaceable>
       </TERM>
       <LISTITEM>
	<PARA>

     <VARIABLELIST>
      <VARLISTENTRY>
       <TERM>
	<ReturnValue>
	 ERROR:  ExecAppend: rejected due to CHECK constraint
	 "<replaceable class="parameter">table_column</replaceable>".
	</ReturnValue>
       </TERM>
       <LISTITEM>
	<PARA>
	 This error occurs at runtime if one tries to insert an illegal
	 value into a column subject to a CHECK constraint.
	</PARA>
       </LISTITEM>
      </VARLISTENTRY>
     </variablelist>
       </LISTITEM>
      </VARLISTENTRY>
     </variablelist>
   </REFSECT3>

   <refsect3>
    <title>Description</title>
    <para>
     The CHECK constraint specifies a restriction on allowed values
within a column.
     The CHECK constraint is also allowed as a table constraint.
    </PARA>
    <PARA>
     The SQL92 CHECK column constraints can only be defined on, and
     refer to, one column of the table. <productname>Postgres</productname>
 does not have
     this restriction.
    </PARA>
   </refsect3>
  </REFSECT2>
  
  <REFSECT2 ID="R2-SQL-PRIMARYKEY-1">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
   <TITLE>
    PRIMARY KEY Constraint
   </TITLE>
   <SYNOPSIS>
[ CONSTRAINT <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE> ] PRIMARY KEY 
   </SYNOPSIS>
   
   <refsect3>
    <title>Inputs</title>
    <PARA>
     <VARIABLELIST>
      <VARLISTENTRY>
       <TERM>
CONSTRAINT <REPLACEABLE CLASS="PARAMETER">name</REPLACEABLE>
       </TERM>
       <LISTITEM>
	<PARA>
	 An arbitrary name for the constraint.
	</PARA>
       </LISTITEM>
      </VARLISTENTRY>
     </VARIABLELIST>
    </para>
   </refsect3>

   <refsect3>
    <title>Outputs</title>
    <variablelist>
     <varlistentry>
      <term>
<returnvalue>ERROR: Cannot insert a duplicate key into a unique index.</returnvalue>
      </term>
      <listitem>
       <para>
	This occurs at run-time if one tries to insert a duplicate value into
	a column subject to a PRIMARY KEY constraint.
      </PARA>
      </listitem>
     </varlistentry>
    </variablelist>
   </refsect3>

   <refsect3>
    <title>Description</title>
    <PARA>
     The PRIMARY KEY column constraint specifies that a column of a table
 may contain only unique
     (non-duplicate), non-NULL values. The definition of
     the specified column does not have to include an explicit NOT NULL
     constraint to be included in a PRIMARY KEY constraint. 
    </PARA>
    <PARA>
     Only one PRIMARY KEY can be specified for a table.
    </PARA>
   </REFSECT3>
   
   <REFSECT3 ID="R3-SQL-PRIMARYKEY-3">
    <REFSECT3INFO>
     <DATE>1998-09-11</DATE>
    </REFSECT3INFO>
    <TITLE>
     Notes
    </TITLE>
    <PARA>
     <productname>Postgres</productname> automatically creates
 a unique index to assure
     data integrity. (See CREATE INDEX statement)
    </PARA>
    <PARA>
     The PRIMARY KEY constraint should name a set of columns that is
     different from other sets of columns named by any UNIQUE constraint
     defined for the same table, since it will result in duplication
of equivalent indexes and unproductive additional runtime overhead.
However, <productname>Postgres</productname> does not specifically
disallow this.
    </PARA>
   </refsect3>

  <REFSECT1 ID="R1-SQL-TABLECONSTRAINT-1">
   <REFSECT1INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT1INFO>
   <TITLE>
   Table CONSTRAINT Clause
   </TITLE>
   <para>
   <SYNOPSIS>
[ CONSTRAINT name ] { PRIMARY KEY |  UNIQUE } ( <replaceable class="parameter">column</replaceable> [, ...] )
[ CONSTRAINT name ] CHECK ( <replaceable>constraint</replaceable> )
   </SYNOPSIS>
   <PARA>

  <REFSECT2 ID="R2-SQL-TABLECONSTRAINT-1">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
<title>
Inputs
</title>

<para>
       <VARIABLELIST>
	<VARLISTENTRY>
	 <TERM>
	 CONSTRAINT <replaceable class="parameter">name</replaceable>
	 </TERM>
	 <LISTITEM>
	  <PARA>
          An arbitrary name given to an integrity constraint.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
	<VARLISTENTRY>
	 <TERM>
	   <replaceable class="parameter">column</replaceable> [, ...]
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   The column name(s) for which to define a unique index
and, for PRIMARY KEY, a NOT NULL constraint.
	  </PARA>
	 </LISTITEM>
	<VARLISTENTRY>
	 <TERM>
	  CHECK ( <replaceable class="parameter">constraint</replaceable> )
	 </TERM>
	 <LISTITEM>
	  <PARA>
	   A boolean expression to be evaluated as the constraint.
	  </PARA>
	 </LISTITEM>
	</VARLISTENTRY>
       </VARIABLELIST>

  <REFSECT2 ID="R2-SQL-TABLECONSTRAINT-2">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
<title>
Outputs
</title>

<para>
The possible outputs for the table constraint clause are the same
as for the corresponding portions of the column constraint clause.

  <REFSECT2 ID="R2-SQL-TABLECONSTRAINT-3">
   <REFSECT2INFO>
    <DATE>1998-09-11</DATE>
   </REFSECT2INFO>
<title>
Description
</title>

   <para>
    A table constraint is an integrity constraint defined on one or
    more columns of a base table. The four variations of "Table
    Constraint" are:
    <simplelist columns="1">
     <member>UNIQUE</member>
     <member>CHECK</member>
     <member>PRIMARY KEY</member>
     <member>FOREIGN KEY</member>
    </simplelist>
   </para>
   <note>