<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v 1.45 2001/09/03 12:57:49 petere Exp $
Postgres documentation
-->

<refentry id="SQL-CREATETABLE">
 <refmeta>
  <refentrytitle id="sql-createtable-title">
   CREATE TABLE
  </refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   CREATE TABLE
  </refname>
  <refpurpose>
   define a new table
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>2001-01-11</date>
  </refsynopsisdivinfo>
  <synopsis>
CREATE [ TEMPORARY | TEMP ] TABLE <replaceable class="PARAMETER">table_name</replaceable> (
    { <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
      | <replaceable>table_constraint</replaceable> }  [, ... ] )
    [ INHERITS ( <replaceable>parent_table</replaceable> [, ... ] ) ]
    [ WITH OIDS | WITHOUT OIDS ]

where <replaceable class="PARAMETER">column_constraint</replaceable> can be:
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY | DEFAULT <replaceable class="PARAMETER">value</replaceable> | CHECK (<replaceable class="PARAMETER">condition</replaceable>) |
  REFERENCES <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL ]
   [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ]
   [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
}

and <replaceable class="PARAMETER">table_constraint</replaceable> can be:
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
{ UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) |
  PRIMARY KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) |
  CHECK ( <replaceable class="PARAMETER">condition</replaceable> ) |
  FOREIGN KEY ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ... ] ) ]
   [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE <replaceable class="parameter">action</replaceable> ] [ ON UPDATE <replaceable class="parameter">action</replaceable> ]
   [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
}
  </synopsis>
  
  <refsect2 id="R2-SQL-CREATETABLE-1">
   <title>
    Inputs
   </title>

   <para>

    <variablelist>
     <varlistentry>
      <term>TEMPORARY or TEMP</term>
      <listitem>
       <para>
	If specified, the table is created only for this session, and is
	automatically dropped on session exit.
	Existing permanent tables with the same name are not visible
	(in this session) while the temporary table exists.
	Any indexes created on a temporary table are automatically
	temporary as well.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">table_name</replaceable></term>
      <listitem>
       <para>
	The name of the new table to be created.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">column_name</replaceable></term>
      <listitem>
       <para>
	The name of a column to be created in the new table.
       </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><replaceable class="PARAMETER">parent_table</replaceable></term>
      <listitem>
       <para>
	The optional INHERITS clause specifies a list of table
	names from which this table automatically inherits all fields.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>WITH OIDS or WITHOUT OIDS</term>
      <listitem>
       <para>
        This optional clause specifies whether rows of the new table should
	have OIDs (object identifiers) assigned to them.  The default is
	WITH OIDS.  (If the new table inherits from any tables that have OIDs,
	then WITH OIDS is forced even if the command says WITHOUT OIDS.)
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">constraint_name</replaceable></term>
      <listitem>
       <para>
        An optional name for a column or table constraint.  If not specified,
	the system generates a name.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><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 class="PARAMETER">condition</replaceable></term>
      <listitem>
       <para>
	CHECK clauses specify integrity constraints or tests which new or
	updated rows must satisfy for an insert or update operation to
	succeed. Each constraint must be an expression producing
	a boolean result.
	A condition appearing within a column definition should reference
	that column's value only, while a condition appearing as a table
	constraint may reference multiple columns.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">table</replaceable></term>
      <listitem>
       <para>
        The name of an existing table to be referenced by a foreign
	key constraint.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">column</replaceable></term>
      <listitem>
       <para>
        The name of a column in an existing table to be referenced by a
	foreign key constraint.  If not specified, the primary key of
	the existing table is assumed.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">action</replaceable></term>
      <listitem>
       <para>
        A keyword indicating the action to take when a foreign key
	constraint is violated.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>    
  </refsect2>

  <refsect2 id="R2-SQL-CREATETABLE-2">
   <title>
    Outputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term><computeroutput>
CREATE
       </computeroutput></term>
      <listitem>
       <para>
	Message returned if table is successfully created.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><computeroutput>
ERROR
       </computeroutput></term>
      <listitem>
       <para>
	Message returned if table creation failed.
	This is usually accompanied by some descriptive text, such as:
	<computeroutput>
ERROR:  Relation '<replaceable class="parameter">table</replaceable>' already exists
	</computeroutput>
	, which occurs at runtime if the table specified already exists
	in the database.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
 </refsynopsisdiv>

 <refsect1 id="R1-SQL-CREATETABLE-1">
  <title>
   Description
  </title>

  <para>
   <command>CREATE TABLE</command> will enter a new, initially empty table
   into the current database. The table will be "owned" by the user issuing the
   command.
  </para>

  <para>
   Each <replaceable class="PARAMETER">type</replaceable>
   may be a simple type, a complex type (set) or an array type.
   Each attribute may be specified to be non-null and
   each may have a default value, specified by the
   <xref linkend="R1-SQL-DEFAULTCLAUSE-1" endterm="R1-SQL-DEFAULTCLAUSE-1-TITLE">.
  </para>

  <para>
   <note>
    <para>
     Consistent array dimensions within an
     attribute are not enforced. This will likely change in a future
     release.
    </para>
   </note>
  </para>

  <para>
   <command>CREATE TABLE</command> also automatically creates a data type
   that represents the tuple type (structure type) corresponding to one
   row of the table.  Therefore, tables can't have the same name as any
   existing datatype.
  </para>

  <para>
   A table can have no more than 1600 columns (in practice, the
   effective limit is lower because of tuple-length constraints).
   A table cannot have the same name as a system catalog table.
  </para>
 </refsect1>

 <refsect1 id="R1-SQL-INHERITSCLAUSE-1">
  <title id="R1-SQL-INHERITSCLAUSE-1-TITLE">
   INHERITS Clause
  </title>
  <para>
   <synopsis>
INHERITS ( <replaceable class="PARAMETER">parent_table</replaceable> [, ... ] )
   </synopsis>
  </para>

  <para>
   The optional INHERITS
   clause specifies a list of table names from which the new table
   automatically inherits all fields.  If the same field name appears in
   more than one parent table, Postgres reports an error unless the field
   definitions match in each of the parent tables.  If there is no
   definition conflict, then the duplicate fields are merged to form a single
   field of the new table.  If the new table's own field list contains a
   field name that is also inherited, this declaration must likewise match
   the inherited field(s), and the field definitions are merged into one.
  </para>

  <para>
   Inherited and new field declarations of the same name must specify exactly
   the same data type to avoid an error.  They need not specify identical
   constraints --- all constraints provided from any declaration are merged
   together and all are applied to the new table.  If the new table explicitly
   specifies a default value for the field, this default overrides any
   defaults from inherited declarations of the field.  Otherwise, any parents
   that specify default values for the field must all specify the same
   default, or an error will be reported.
  </para>

  <para>
   Postgres automatically allows the created table to inherit functions on
   tables above it in the inheritance hierarchy; that is, if we create table
   <literal>foo</literal> inheriting from <literal>bar</literal>, then
   functions that accept the tuple type <literal>bar</literal> can also be
   applied to instances of <literal>foo</literal>.  (Currently, this works
   reliably for functions on the first or only parent table, but not so well
   for functions on additional parents.)
  </para>
 </refsect1>

 <refsect1 id="R1-SQL-OIDSCLAUSE-1">
  <title id="R1-SQL-OIDSCLAUSE-1-TITLE">
   OIDS Clause
  </title>
  <para>
   <synopsis>
    WITH OIDS | WITHOUT OIDS
   </synopsis>
  </para>

  <para>
   This clause controls whether an OID (object ID) is generated and assigned
   to each row inserted into the table.  The default is WITH OIDS.
   Specifying WITHOUT OIDS allows the user to suppress generation of
   OIDs for rows of a table.  This may be worthwhile for large
   tables, since it will reduce OID consumption and thereby postpone
   wraparound of the 32-bit OID counter.  Once the counter wraps around,
   uniqueness of OIDs can no longer be assumed, which considerably reduces
   their usefulness.
  </para>

  <para>
   Whenever an application makes use of OIDs to identify specific rows of
   a table, it is recommended that you create a unique index on OID for
   that table, to ensure that OIDs in the table will indeed uniquely
   identify rows even after counter wraparound.  (An index on OID is needed
   anyway for fast lookup of rows by OID.)  Avoid assuming that OIDs are
   unique across tables --- if you need a database-wide unique identifier,
   use the combination of tableoid and row OID for the purpose.  (It is
   likely that future Postgres releases will use a separate OID counter
   for each table, so that it will be <emphasis>necessary</> not optional
   to include tableoid to have a unique identifier database-wide.)
  </para>

  <tip>
   <para>
    WITHOUT OIDS is not recommended for tables with no primary key, since
    without either an OID or a unique data key, it is difficult to identify
    specific rows.
   </para>
  </tip>
 </refsect1>

 <refsect1 id="R1-SQL-DEFAULTCLAUSE-1">
  <title id="R1-SQL-DEFAULTCLAUSE-1-TITLE">
   DEFAULT Clause
  </title>
  <para>
   <synopsis>
DEFAULT <replaceable class="PARAMETER">value</replaceable>
   </synopsis>
  </para>

  <para>
   The DEFAULT clause assigns a default data value for the column whose
   column definition it appears within.  The value is any variable-free
   expression (note that sub-selects and cross-references to other
   columns in the current table are not supported).
   The data type of a default value must match the column definition's
   data type.
  </para>

  <para>
   The DEFAULT expression will be used in any INSERT operation that does
   not specify a value for the column.  If there is no DEFAULT clause,
   then the default is NULL.
  </para>

  <refsect2 id="R2-SQL-DEFAULTCLAUSE-2">
   <title>
    Usage
   </title>

   <para>
    <programlisting>
CREATE TABLE distributors (
    name     VARCHAR(40) DEFAULT 'luso films',
    did      INTEGER  DEFAULT NEXTVAL('distributors_serial'),
    modtime  TIMESTAMP DEFAULT now()
);
    </programlisting>
    The above assigns a literal constant default value for the column
    <literal>name</literal>, and arranges for the default value of column
    <literal>did</literal> to be generated by selecting the next value of a
    sequence object.  The default value of <literal>modtime</literal> will
    be the time at which the row is inserted.
   </para>

   <para>
    It is worth remarking that
    <programlisting>
    modtime  TIMESTAMP DEFAULT 'now'
    </programlisting>
    would produce a result that is probably not the intended one: the
    string <literal>'now'</literal> will be coerced to a timestamp value
    immediately, and so the default value of <literal>modtime</literal> will
    always be the time of table creation.  This difficulty is avoided by
    specifying the default value as a function call.
   </para>
  </refsect2>
 </refsect1>

 <refsect1 id="R1-SQL-COLUMNCONSTRAINT-1">
  <title id="R1-SQL-COLUMNCONSTRAINT-1-TITLE">
   Column Constraints
  </title>
  <para>
   <synopsis>
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] {
    NULL | NOT NULL | UNIQUE | PRIMARY KEY | CHECK <replaceable
     class="parameter">condition</replaceable> |
    REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ]
     [ MATCH <replaceable class="parameter">matchtype</replaceable> ]
     [ ON DELETE <replaceable class="parameter">action</replaceable> ] 
     [ ON UPDATE <replaceable class="parameter">action</replaceable> ]
     [ [ NOT ] DEFERRABLE ] 
     [ INITIALLY <replaceable class="parameter">checktime</replaceable> ] }
   </synopsis>
  </para>

  <refsect2 id="R2-SQL-COLUMNCONSTRAINT-1">
   <title>
    Inputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term><replaceable class="parameter">constraint_name</replaceable></term>
      <listitem>
       <para>
	An arbitrary name given to a constraint clause.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>NULL</term>
      <listitem>
       <para>
	The column is allowed to contain NULL values. This is the default.
       </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 automatic creation of a unique index on the column.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>PRIMARY KEY</term>
      <listitem>
       <para>
	This column is a primary key, which implies that other tables may rely
	on this column as a unique identifier for rows.  Both UNIQUE and
	NOT NULL are implied by PRIMARY KEY.  See PRIMARY KEY for more
	information.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>
<replaceable class="parameter">condition</replaceable>
      </term>
      <listitem>
       <para>
	An arbitrary boolean-valued constraint condition.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

  <refsect2 id="R2-SQL-COLUMNCONSTRAINT-2">
   <title>
    Description
   </title>

   <para>
    The optional constraint clauses specify constraints or tests which
    new or updated rows must satisfy for an insert or update
    operation to succeed.
   </para>

   <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 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>
  </refsect2>
   
  <refsect2 id="R2-SQL-NOTNULL-1">
   <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. 
    This is a column constraint only, and not allowed
    as a table constraint.
   </para>

   <refsect3 id="R3-SQL-NOTNULL-1">
    <title>
     Outputs
    </title>
    <para>
     <variablelist>
      <varlistentry>
       <term><replaceable>status</replaceable></term>
       <listitem>
	<para>
	 <variablelist>
	  <varlistentry>
	   <term><computeroutput>
ERROR:  ExecAppend: Fail to add null value in not null attribute "<replaceable class="parameter">column</replaceable>".
	    </computeroutput></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>
	</para>
       </listitem>
      </varlistentry>
     </variablelist> 
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-NOTNULL-2">
    <title>
     Description
    </title>
    <para>
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-NOTNULL-3">
    <title>
     Usage
    </title>

    <para>
     Define two NOT NULL column constraints on the table
     <classname>distributors</classname>,
     one of which is explicitly given a name:

     <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(3) CONSTRAINT no_null NOT NULL,
    name     VARCHAR(40) NOT NULL
);
     </programlisting>
    </para>
   </refsect3>
  </refsect2>

  <refsect2 id="R2-SQL-UNIQUECLAUSE-1">
   <title>
    UNIQUE Constraint
   </title>
   <synopsis>
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] UNIQUE
   </synopsis>

   <refsect3>
    <title>Inputs</title>
    <para>

     <variablelist>
      <varlistentry>
       <term><replaceable class="parameter">constraint_name</replaceable></term>
       <listitem>
	<para>
	 An arbitrary name given to a constraint clause.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>
   
   <refsect3>
    <title>Outputs</title>
    <para>
     <variablelist>
      <varlistentry>
       <term><replaceable>status</replaceable></term>
       <listitem>
	<para>
	 <variablelist>
	  <varlistentry>
	   <term><computeroutput>
ERROR: Cannot insert a duplicate key into a unique index.
	    </computeroutput></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>
    </para>
   </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>

   <refsect3 id="R3-SQL-UNIQUECLAUSE-3">
    <title>
     Usage
    </title>

    <para>
     Defines a UNIQUE constraint for the <literal>name</literal> column:
     <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>
    </para>
   </refsect3>
  </refsect2>

  <refsect2 id="R2-SQL-CHECK-1">
   <title>
    The CHECK Constraint
   </title>
   <synopsis>
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] CHECK ( <replaceable>condition</replaceable> )
   </synopsis>
   <refsect3 id="R3-SQL-CHECK-1">
    <title>Inputs</title>
    <para>

     <variablelist>
      <varlistentry>
       <term><replaceable class="parameter">constraint_name</replaceable></term>
       <listitem>
	<para>
	 An arbitrary name given to a constraint clause.
	</para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term><replaceable>condition</replaceable></term>
       <listitem>
	<para>
	 Any valid conditional expression evaluating to a boolean result.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-CHECK-2">
    <title>
     Outputs
    </title>
    <para>

     <variablelist>
      <varlistentry>
       <term><replaceable>status</replaceable></term>
       <listitem>
	<para>

	 <variablelist>
	  <varlistentry>
	   <term><computeroutput>
ERROR:  ExecAppend: rejected due to CHECK constraint "<replaceable class="parameter">constraint_name</replaceable>".
	    </computeroutput></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>
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>

   <refsect3>
    <title>Description</title>
    <para>
     The CHECK constraint specifies a generic restriction on allowed values
     within a column. The CHECK constraint is also allowed as a table
     constraint.
    </para>
    <para>
     CHECK specifies a general boolean expression involving one or more
     columns of a table.  A new row will be rejected if the boolean
     expression evaluates to FALSE when applied to the row's values.
    </para>
    <para>
     Currently, CHECK expressions cannot contain sub-selects nor refer
     to variables other than fields of the current row.
    </para>
    <para>
     The SQL92 standard says that CHECK column constraints may only refer
     to the column they apply to; only CHECK table constraints may refer
     to multiple columns.
     <productname>Postgres</productname> does not enforce this restriction.
     It treats column and table CHECK constraints alike.
    </para>
   </refsect3>
  </refsect2>
  
  <refsect2 id="R2-SQL-PRIMARYKEY-1">
   <title>
    PRIMARY KEY Constraint
   </title>
   <synopsis>
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ] PRIMARY KEY 
   </synopsis>

   <refsect3>
    <title>Inputs</title>
    <para>
     <variablelist>
      <varlistentry>
       <term><replaceable class="PARAMETER">constraint_name</replaceable></term>
       <listitem>
	<para>
	 An arbitrary name given to a constraint clause.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>

   <refsect3>
    <title>Outputs</title>
    <variablelist>
     <varlistentry>
      <term><computeroutput>
ERROR: Cannot insert a duplicate key into a unique index.
       </computeroutput></term>
      <listitem>
       <para>
	This occurs at runtime 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, whether as a
     column constraint or a table constraint.
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-PRIMARYKEY-3">
    <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>
  </refsect2>

  <refsect2 id="R2-SQL-REFERENCES-1">
   <refsect2info>
    <date>2000-02-04</date>
   </refsect2info>
   <title>
    REFERENCES Constraint
   </title>
   <synopsis>
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] 
    [ MATCH <replaceable class="parameter">matchtype</replaceable> ]
    [ ON DELETE <replaceable class="parameter">action</replaceable> ] 
    [ ON UPDATE <replaceable class="parameter">action</replaceable> ]
    [ [ NOT ] DEFERRABLE ] 
    [ INITIALLY <replaceable class="parameter">checktime</replaceable> ]
   </synopsis>
   <para>
    The REFERENCES constraint specifies a rule that a column
    value is checked against the values of another column.
    REFERENCES can also be specified as part of
    a FOREIGN KEY table constraint.
   </para>

   <refsect3 id="R3-SQL-REFERENCES-1">
    <title>Inputs</title>

    <para>
     <variablelist>
      <varlistentry>
       <term><replaceable class="PARAMETER">constraint_name</replaceable></term>
       <listitem>
	<para>
	 An arbitrary name given to a constraint clause.
	</para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term><replaceable class="parameter">reftable</replaceable></term>
       <listitem>
        <para>
         The table that contains the data to check against.
        </para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term><replaceable class="parameter">refcolumn</replaceable></term>
       <listitem>
        <para>
         The column in <replaceable class="parameter">reftable</replaceable>
         to check the data against.  If this is not specified, the PRIMARY KEY of the
         <replaceable class="parameter">reftable</replaceable> is used.
        </para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term>MATCH <replaceable class="parameter">matchtype</replaceable></term>
       <listitem>
        <para>
         There are three match types: MATCH FULL, MATCH PARTIAL, and a
         default match type if none is specified. MATCH FULL will not
         allow one column of a multi-column foreign key to be NULL
         unless all foreign key columns are NULL. The default MATCH type
         allows some foreign key columns to be NULL while other parts
         of the foreign key are not NULL. MATCH PARTIAL is currently not
	 supported.
        </para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term>ON DELETE <replaceable class="parameter">action</replaceable></term>
       <listitem>
        <para>
	 The action to do when a referenced row in the referenced table is being
         deleted.  There are the following actions.
         <variablelist>
          <varlistentry>
           <term>NO ACTION</term>
           <listitem>
            <para>
             Produce error if foreign key violated.  This is the default.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>RESTRICT</term>
           <listitem>
            <para>
             Same as NO ACTION.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>CASCADE</term>
           <listitem>
            <para>
             Delete any rows referencing the deleted row.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>SET NULL</term>
           <listitem>
            <para>
             Set the referencing column values to NULL.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>SET DEFAULT</term>
           <listitem>
            <para>
             Set the referencing column values to their default value.
            </para>
           </listitem>
          </varlistentry>
         </variablelist>
        </para>
       </listitem>
      </varlistentry>      
      <varlistentry>
       <term>ON UPDATE <replaceable class="parameter">action</replaceable></term>
       <listitem>
        <para>
	 The action to do when a referenced column in the referenced
	 table is being updated to a new value. If the row is updated,
	 but the referenced column is not changed, no action is done.
	 There are the following actions.
         <variablelist>
          <varlistentry>
           <term>NO ACTION</term>
           <listitem>
            <para>
             Produce error if foreign key violated.  This is the default.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>RESTRICT</term>
           <listitem>
            <para>
             Same as NO ACTION.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>CASCADE</term>
           <listitem>
            <para>
             Update the value of the referencing column to the new value of the
             referenced column.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>SET NULL</term>
           <listitem>
            <para>
             Set the referencing column values to NULL.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>SET DEFAULT</term>
           <listitem>
            <para>
             Set the referencing column values to their default value.
            </para>
           </listitem>
          </varlistentry>
         </variablelist>
        </para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term> [ NOT ] DEFERRABLE </term>
       <listitem>
        <para>
         This controls whether the constraint can be deferred to the end
         of the transaction. If DEFERRABLE, SET CONSTRAINTS ALL DEFERRED
         will cause the foreign key to be checked only at the end of the
         transaction. NOT DEFERRABLE is the default.
        </para>
       </listitem>
      </varlistentry>

      <varlistentry>
       <term>INITIALLY <replaceable class="parameter">checktime</replaceable></term>
       <listitem>
        <para>
         <replaceable class="parameter">checktime</replaceable> has two possible values 
         which specify the default time to check the constraint.
         <variablelist>
          <varlistentry>
           <term>DEFERRED</term>
	   <listitem>
	    <para>
	     Check constraint only at the end of the transaction.
	    </para>
	   </listitem>
          </varlistentry>
          <varlistentry>
           <term>IMMEDIATE</term>
	   <listitem>
	    <para>
	     Check constraint after each statement. This is the default.
	    </para>
	   </listitem>
	  </varlistentry>
         </variablelist>
        </para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>
   

   <refsect3 id="R3-SQL-REFERENCES-2">
    <refsect3info>
     <date>2000-02-04</date>
    </refsect3info>
    <title>
     Outputs
    </title>
    <para>
     <variablelist>
      <varlistentry>
       <term><replaceable>status</replaceable></term>
       <listitem>
	<para>
	 <variablelist>
	  <varlistentry>
	   <term><computeroutput>
ERROR:  <replaceable class="parameter">name</replaceable> referential integrity violation - key referenced from 
<replaceable class="parameter">table</replaceable> not found in <replaceable class="parameter">reftable</replaceable>
	    </computeroutput></term>
	   <listitem>
	    <para>
             This error occurs at runtime if one tries to insert a value
             into a column which does not have a matching column in the
             referenced table.
	    </para>
	   </listitem>
	  </varlistentry>
	 </variablelist>
	</para>
       </listitem>
      </varlistentry>
     </variablelist> 
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-REFERENCES-3">
    <title>Description</title>
    <para>
     The REFERENCES column constraint specifies that a
     column of a table must only contain values which match against
     values in a referenced column of a referenced table.
    </para>
    <para>
     A value added to this column is matched against the values of the
     referenced table and referenced column using the given match type.
     In addition, when the referenced column data is changed, actions
     are run upon this column's matching data.
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-REFERENCES-4">
    <refsect3info>
     <date>1998-09-11</date>
    </refsect3info>
    <title>
     Notes
    </title>
    <para>
     Currently <productname>Postgres</productname> only supports MATCH
     FULL and a default match type. In addition, the referenced
     columns are supposed to be the columns of a UNIQUE constraint in
     the referenced table, however <productname>Postgres</productname>
     does not enforce this.
    </para>
   </refsect3>
  </refsect2>
 </refsect1>
 
 <refsect1 id="R1-SQL-TABLECONSTRAINT-1">
  <title>
   Table Constraints
  </title>
  <para>
   <synopsis>
[ CONSTRAINT name ] { PRIMARY KEY |  UNIQUE } ( <replaceable class="parameter">column</replaceable> [, ... ] )
[ CONSTRAINT name ] CHECK ( <replaceable>constraint</replaceable> )
[ CONSTRAINT name ] FOREIGN KEY ( <replaceable class="parameter">column</replaceable> [, ... ] ) 
                     REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
                      [ MATCH <replaceable class="parameter">matchtype</replaceable> ] 
                      [ ON DELETE <replaceable class="parameter">action</replaceable> ] 
                      [ ON UPDATE <replaceable class="parameter">action</replaceable> ]
                      [ [ NOT ] DEFERRABLE ] 
                      [ INITIALLY <replaceable class="parameter">checktime</replaceable> ]
   </synopsis>
  </para>
  <refsect2 id="R2-SQL-TABLECONSTRAINT-1">
   <title>
    Inputs
   </title>

   <para>

    <variablelist>
     <varlistentry>
      <term><replaceable class="parameter">constraint_name</replaceable></term>
      <listitem>
       <para>
	An arbitrary name given to a constraint clause.
       </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>
     <varlistentry>
      <term>CHECK ( <replaceable class="parameter">constraint</replaceable> )</term>
      <listitem>
       <para>
	A boolean expression to be evaluated as the constraint.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
    
  <refsect2 id="R2-SQL-TABLECONSTRAINT-2">
   <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.
   </para>
  </refsect2>
  
  <refsect2 id="R2-SQL-TABLECONSTRAINT-3">
   <title>
    Description
   </title>
   
   <para>
    A table constraint is an integrity constraint defined on one or
    more columns of a 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>
  </refsect2>
 
  <refsect2 id="R2-SQL-UNIQUECLAUSE-4">
   <title>
    UNIQUE Constraint
   </title>
   <para>
    <synopsis>
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] UNIQUE ( <replaceable class="parameter">column</replaceable> [, ... ] )
    </synopsis>
   </para>
   <refsect3>
    <title>Inputs</title>
    <variablelist>
     <varlistentry>
      <term><replaceable class="parameter">constraint_name</replaceable></term>
      <listitem>
       <para>
	An arbitrary name given to a constraint clause.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="parameter">column</replaceable></term>
      <listitem>
       <para>
	A name of a column in a table.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </refsect3>

   <refsect3>
    <title>Outputs</title>
    <para>
     <variablelist>
      <varlistentry>
       <term><replaceable>status</replaceable></term>
       <listitem>
	<para>
	 <variablelist>
	  <varlistentry>
	   <term>ERROR: Cannot insert a duplicate key into a unique index</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>
    </para>
   </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. The
     behavior of the UNIQUE table constraint is the same as that for
     column constraints, with the additional capability to span multiple
     columns.
    </para>
    <para>
     See the section on the UNIQUE column constraint for more details.
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-UNIQUECLAUSE-4">
    <title>
     Usage
    </title>
    
    <para>
     Prevent duplicate rows in the table distributors:
     <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(3),
    name     VARCHAR(40),
    UNIQUE(did,name)
);
     </programlisting>
    </para>
   </refsect3>
  </refsect2>

  <refsect2 id="R2-SQL-PRIMARYKEY-4">
   <title>
    PRIMARY KEY Constraint
   </title>
   <para>
    <synopsis>
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ] PRIMARY KEY ( <replaceable class="PARAMETER">column</replaceable> [, ... ] ) 
    </synopsis>
   </para>
   <refsect3>
    <title>Inputs</title>
    <para>

     <variablelist>
      <varlistentry>
       <term><replaceable class="PARAMETER">constraint_name</replaceable></term>
       <listitem>
	<para>
	 An arbitrary name given to a constraint clause.
	</para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term><replaceable class="PARAMETER">column</replaceable> [, ... ]</term>
       <listitem>
	<para>
	 The names of one or more columns in the table.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>
   
   <refsect3>
    <title>Outputs</title>
    <variablelist>
     <varlistentry>
      <term><replaceable>status</replaceable></term>
      <listitem>
       <para>
	<variablelist>
	 <varlistentry>
	  <term>ERROR: Cannot insert a duplicate key into a unique index.</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>
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </refsect3>
   
   <refsect3>
    <title>Description</title>
    <para>
     The PRIMARY KEY constraint specifies a rule that a group of one
     or more distinct columns of a table may contain only unique
     (nonduplicate), non-null values. The column definitions of
     the specified columns do not have to include a NOT NULL
     constraint to be included in a PRIMARY KEY constraint.
    </para>

    <para>
     The PRIMARY KEY table constraint is similar to that for column constraints,
     with the additional capability of encompassing multiple columns.
    </para>
    <para>
     Refer to the section on the PRIMARY KEY column constraint for more
     information.
    </para>
   </refsect3>
  </refsect2>

  <refsect2 id="R2-SQL-REFERENCES-2">
   <refsect2info>
    <date>2000-02-04</date>
   </refsect2info>
   <title>
    REFERENCES Constraint
   </title>
   <synopsis>
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] FOREIGN KEY ( <replaceable class="parameter">column</replaceable> [, ... ] )
    REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ] 
    [ MATCH <replaceable class="parameter">matchtype</replaceable> ]
    [ ON DELETE <replaceable class="parameter">action</replaceable> ] 
    [ ON UPDATE <replaceable class="parameter">action</replaceable> ]
    [ [ NOT ] DEFERRABLE ]
    [ INITIALLY <replaceable class="parameter">checktime</replaceable> ]
   </synopsis>
   <para>
    The REFERENCES constraint specifies a rule that a column value or set
    of column values is
    checked against the values in another table.
   </para>

   <refsect3 id="R3-SQL-REFERENCES-5">
    <title>Inputs</title>
    <para>
     <variablelist>
      <varlistentry>
       <term><replaceable class="PARAMETER">constraint_name</replaceable></term>
       <listitem>
	<para>
	 An arbitrary name given to a constraint clause.
	</para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term><replaceable class="PARAMETER">column</replaceable> [, ... ]</term>
       <listitem>
	<para>
	 The names of one or more columns in the table.
	</para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term><replaceable class="parameter">reftable</replaceable></term>
       <listitem>
        <para>
         The table that contains the data to check against.
        </para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term><replaceable class="parameter">referenced column</replaceable> [, ... ]</term>
       <listitem>
        <para>
         One or more columns in the <replaceable class="parameter">reftable</replaceable>
         to check the data against.  If this is not specified, the PRIMARY KEY of the
         <replaceable class="parameter">reftable</replaceable> is used.
        </para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term>MATCH <replaceable class="parameter">matchtype</replaceable></term>
       <listitem>
        <para>
         There are three match types: MATCH FULL, MATCH PARTIAL, and a
         default match type if none is specified. MATCH FULL will not
         allow one column of a multi-column foreign key to be NULL
         unless all foreign key columns are NULL. The default MATCH type
         allows some foreign key columns to be NULL while other parts
         of the foreign key are not NULL. MATCH PARTIAL is currently not
	 supported.
        </para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term>ON DELETE <replaceable class="parameter">action</replaceable></term>
       <listitem>
        <para>
	 The action to do when a referenced row in the referenced table is being
         deleted.  There are the following actions.
         <variablelist>
          <varlistentry>
           <term>NO ACTION</term>
           <listitem>
            <para>
             Produce error if foreign key violated.  This is the default.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>RESTRICT</term>
           <listitem>
            <para>
             Same as NO ACTION.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>CASCADE</term>
           <listitem>
            <para>
             Delete any rows referencing the deleted row.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>SET NULL</term>
           <listitem>
            <para>
             Set the referencing column values to NULL.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>SET DEFAULT</term>
           <listitem>
            <para>
             Set the referencing column values to their default value.
            </para>
           </listitem>
          </varlistentry>
         </variablelist>
        </para>
       </listitem>
      </varlistentry>      
      <varlistentry>
       <term>ON UPDATE <replaceable class="parameter">action</replaceable></term>
       <listitem>
        <para>
	 The action to do when a referenced column in the referenced
	 table is being updated to a new value. If the row is updated,
	 but the referenced column is not changed, no action is done.
	 There are the following actions.
         <variablelist>
          <varlistentry>
           <term>NO ACTION</term>
           <listitem>
            <para>
             Produce error if foreign key violated.  This is the default.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>RESTRICT</term>
           <listitem>
            <para>
             Disallow update of row being referenced.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>CASCADE</term>
           <listitem>
            <para>
             Update the value of the referencing column to the new value
             of the referenced column.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>SET NULL</term>
           <listitem>
            <para>
             Set the referencing column values to NULL.
            </para>
           </listitem>
          </varlistentry>
          <varlistentry>
           <term>SET DEFAULT</term>
           <listitem>
            <para>
             Set the referencing column values to their default value.
            </para>
           </listitem>
          </varlistentry>
         </variablelist>
        </para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term> [ NOT ] DEFERRABLE </term>
       <listitem>
        <para>
         This controls whether the constraint can be deferred to the end
         of the transaction. If DEFERRABLE, SET CONSTRAINTS ALL DEFERRED
         will cause the foreign key to be checked only at the end of the
         transaction. NOT DEFERRABLE is the default.
        </para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term>INITIALLY <replaceable class="parameter">checktime</replaceable></term>
       <listitem>
        <para>
         <replaceable class="parameter">checktime</replaceable> has two
         possible values which specify the default time to check the
         constraint.
         <variablelist>
          <varlistentry>
           <term>IMMEDIATE</term>
	   <listitem>
	    <para>
	     Check constraint after each statement. This is the default.
	    </para>
	   </listitem>
          </varlistentry>
          <varlistentry>
           <term>DEFERRED</term>
	   <listitem>
	    <para>
	     Check constraint only at the end of the transaction.
	    </para>
	   </listitem>
          </varlistentry>
         </variablelist>
        </para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>
   <refsect3 id="R3-SQL-REFERENCES-6">
    <refsect3info>
     <date>2000-02-04</date>
    </refsect3info>
    <title>
     Outputs
    </title>
    <para>
     <variablelist>
      <varlistentry>
       <term><replaceable>status</replaceable></term>
       <listitem>
	<para>
	 <variablelist>
	  <varlistentry>
	   <term><computeroutput>
ERROR:  <replaceable class="parameter">name</replaceable> referential integrity violation - key referenced from 
<replaceable class="parameter">table</replaceable> not found in <replaceable class="parameter">reftable</replaceable>
	    </computeroutput></term>
	   <listitem>
	    <para>
             This error occurs at runtime if one tries to insert a value
             into a column which does not have a matching column in the
             referenced table.
	    </para>
	   </listitem>
	  </varlistentry>
	 </variablelist>
	</para>
       </listitem>
      </varlistentry>
     </variablelist> 
    </para>
   </refsect3>
   <refsect3>
    <title>Description</title>
    <para>
     The FOREIGN KEY constraint specifies a rule that a group of one
     or more distinct columns of a table is related to a group
     of distinct columns in the referenced table.
    </para>

    <para>
     The FOREIGN KEY table constraint is similar to that for column
     constraints, with the additional capability of encompassing
     multiple columns.
    </para>
    <para>
     Refer to the section on the FOREIGN KEY column constraint for more
     information.
    </para>
   </refsect3>
   
  </refsect2>

 </refsect1>
 
 <refsect1 id="R1-SQL-CREATETABLE-2">
  <title>
   Usage
  </title>
  <para>
   Create table films and table distributors:

   <programlisting>
CREATE TABLE films (
     code      CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY,
     title     CHARACTER VARYING(40) NOT NULL,
     did       DECIMAL(3) NOT NULL,
     date_prod DATE,
     kind      CHAR(10),
     len       INTERVAL HOUR TO MINUTE
);
   </programlisting>

   <programlisting>
CREATE TABLE distributors (
     did      DECIMAL(3) PRIMARY KEY DEFAULT NEXTVAL('serial'),
     name     VARCHAR(40) NOT NULL CHECK (name &lt;&gt; '')
);
   </programlisting>
  </para>

  <para>
   Create a table with a 2-dimensional array:

   <programlisting>
   CREATE TABLE array (
          vector INT[][]
          );
   </programlisting>
  </para>

  <para>
   Define a UNIQUE table constraint for the table films.
   UNIQUE table constraints can be defined on one or more
   columns of the table:

   <programlisting>
CREATE TABLE films (
    code      CHAR(5),
    title     VARCHAR(40),
    did       DECIMAL(3),
    date_prod DATE,
    kind      CHAR(10),
    len       INTERVAL HOUR TO MINUTE,
    CONSTRAINT production UNIQUE(date_prod)
);
   </programlisting>
  </para>
  
  <para>
   Define a CHECK column constraint:

   <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(3) CHECK (did > 100),
    name     VARCHAR(40)
);
   </programlisting>
  </para>

  <para>
   Define a CHECK table constraint:

   <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(3),
    name     VARCHAR(40)
    CONSTRAINT con1 CHECK (did > 100 AND name > '')
);
   </programlisting>
  </para>
 
  <para>
   Define a PRIMARY KEY table constraint for the table films.
   PRIMARY KEY table constraints can be defined on one or more
   columns of the table:

   <programlisting>
CREATE TABLE films (
    code      CHAR(5),
    title     VARCHAR(40),
    did       DECIMAL(3),
    date_prod DATE,
    kind      CHAR(10),
    len       INTERVAL HOUR TO MINUTE,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);
   </programlisting>
  </para>

  <para>
   Defines a PRIMARY KEY column constraint for table distributors.
   PRIMARY KEY column constraints can only be defined on one column
   of the table (the following two examples are equivalent):

   <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(3),
    name     CHAR VARYING(40),
    PRIMARY KEY(did)
); 
   </programlisting>

   <programlisting>
CREATE TABLE distributors (
    did      DECIMAL(3) PRIMARY KEY,
    name     VARCHAR(40)
);
   </programlisting>
  </para>
  
 </refsect1>
 
 <refsect1 id="R1-SQL-CREATETABLE-3">
  <title>
   Compatibility
  </title>

  <refsect2 id="R2-SQL-CREATETABLE-4">
   <title>
    SQL92
   </title>
   <para>
    In addition to the locally visible temporary table, SQL92 also defines a
    CREATE GLOBAL TEMPORARY TABLE statement, and optionally an 
    ON COMMIT clause:
   <synopsis>
CREATE GLOBAL TEMPORARY TABLE <replaceable class="parameter">table</replaceable> ( <replaceable class="parameter">column</replaceable> <replaceable class="parameter">type</replaceable> [
    DEFAULT <replaceable class="parameter">value</replaceable> ] [ CONSTRAINT <replaceable class="parameter">column_constraint</replaceable> ] [, ... ] )
    [ CONSTRAINT <replaceable class="parameter">table_constraint</replaceable> ] [ ON COMMIT { DELETE | PRESERVE } ROWS ] 
   </synopsis>
   </para>

   <para>
    For temporary tables, the CREATE GLOBAL TEMPORARY TABLE statement
    names a new table visible to other clients and defines the table's columns
    and constraints. 
   </para>
   <para>
    The optional ON COMMIT clause of CREATE TEMPORARY TABLE specifies
    whether or not the temporary table should be emptied of rows
    whenever COMMIT is executed. If the ON COMMIT clause is omitted, SQL92
    specifies that the default is ON COMMIT DELETE ROWS.  However,
    <productname>Postgres</productname>' behavior is always like
    ON COMMIT PRESERVE ROWS.
   </para>

   <refsect3 id="R3-SQL-UNIQUECLAUSE-1">
    <title>
     UNIQUE clause
    </title>
    <para>
     SQL92 specifies some additional capabilities for UNIQUE:
    </para>
    <para>
     Table Constraint definition:

     <synopsis>
[ CONSTRAINT <replaceable>constraint_name</replaceable> ] UNIQUE ( <replaceable>column</replaceable> [, ... ] )
    [ { INITIALLY DEFERRED | INITIALLY IMMEDIATE } ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>

    <para>
     Column Constraint definition:

     <synopsis>
[ CONSTRAINT <replaceable>constraint_name</replaceable> ] UNIQUE
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>
   </refsect3>
   
   <refsect3 id="R3-SQL-NULL-1">
    <title>
     NULL clause
    </title>
    <para>
	The NULL "constraint" (actually a non-constraint) is a
	<productname>Postgres</productname> extension to SQL92 that is
	included for symmetry with the NOT NULL clause (and for compatibility
	with some other RDBMSes). Since it is the
	default for any column, its presence is simply noise.
     <synopsis>
[ CONSTRAINT <replaceable>constraint_name</replaceable> ] NULL 
     </synopsis>
    </para>
   </refsect3>
   
   <refsect3 id="R3-SQL-NOTNULL-4">
    <title>
     NOT NULL clause
    </title>
    <para>
     
     SQL92 specifies some additional capabilities for NOT NULL:
     <synopsis>
[ CONSTRAINT <replaceable>constraint_name</replaceable> ] NOT NULL 
    [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>
   </refsect3>
   
<!--
I can't figure out why DEFAULT clause is different from what we already have.
Perhaps because CURRENT_USER and CURRENT_DATE have specific types (currently
the "name" type), if you aren't careful then the types won't match up with
the column. Not our problem...
- Thomas 1998-08-16

   <REFSECT3 ID="R3-SQL-DEFAULTCLAUSE-1">
    <TITLE>
     DEFAULT clause
    </TITLE>
    <PARA>
     SQL92 specifies some additional capabilities for the DEFAULT clause.
     A DEFAULT clause is used to set the default value for a column
     or a domain.
    </para>
    <synopsis>
DEFAULT niladic_user_function | niladic_datetime_function | NULL
    </synopsis>
   </refsect3>
-->

   <refsect3 id="R3-SQL-CONSTRAINT-3">
    <title>
     CONSTRAINT clause
    </title>
    <para>
     SQL92 specifies some additional capabilities for constraints,
     and also defines assertions and domain constraints.
     <note>
      <para>
       <productname>Postgres</productname> does not yet support 
       either domains or assertions.
      </para>
     </note>
    </para>
    <para>
     An assertion is a special type of integrity constraint and shares
     the same namespace as other constraints. However, an assertion is
     not necessarily dependent on one particular table as
     constraints are, so SQL-92 provides the CREATE ASSERTION statement
     as an alternate method for defining a constraint:
    </para>
    <synopsis>
CREATE ASSERTION <replaceable>name</replaceable> CHECK ( <replaceable>condition</replaceable> )
    </synopsis>
    
    <para>
     Domain constraints are defined by CREATE DOMAIN or ALTER DOMAIN
     statements:
    </para>
    <para>
     Domain constraint: 

     <synopsis>
[ CONSTRAINT <replaceable>constraint_name</replaceable> ] CHECK <replaceable>constraint</replaceable> 
    [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>

    <para>
     Table constraint definition:

     <synopsis>
[ CONSTRAINT <replaceable>constraint_name</replaceable> ] { PRIMARY KEY ( <replaceable class="parameter">column</replaceable>, ... ) | FOREIGN KEY <replaceable>constraint</replaceable> | UNIQUE <replaceable>constraint</replaceable> | CHECK <replaceable>constraint</replaceable> }
    [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>

    <para>
     Column constraint definition:

     <synopsis>
[ CONSTRAINT <replaceable>constraint_name</replaceable> ] { NOT NULL | PRIMARY KEY | FOREIGN KEY <replaceable>constraint</replaceable> | UNIQUE | CHECK <replaceable>constraint</replaceable> }  
    [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>

    <para>
     A CONSTRAINT definition may contain one deferment attribute
     clause and/or one initial constraint mode clause, in any order.
     <variablelist>
      <varlistentry>
       <term>NOT DEFERRABLE</term>
       <listitem>
	<para>
         The constraint must be checked at the end of each statement.
         SET CONSTRAINTS ALL DEFERRED will have no effect on this type
         of constraint.
	</para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term>DEFERRABLE</term>
       <listitem>
	<para>
         This controls whether the constraint can be deferred to the end
         of the transaction. If SET CONSTRAINTS ALL DEFERRED is used or
         the constraint is set to INITIALLY DEFERRED, this will cause
         the foreign key to be checked only at the end of the
         transaction.
	</para>
        <note>
         <para>  
          <command>SET CONSTRAINTS</> changes the foreign key constraint mode
          only for the current transaction.
         </para>
        </note>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term>INITIALLY IMMEDIATE</term>
       <listitem>
	<para>
	 Check constraint after each statement. This is the default.
	</para>
       </listitem>
      </varlistentry>
      <varlistentry>
       <term>INITIALLY DEFERRED</term>
       <listitem>
	<para>
         Check constraint only at the end of the transaction.
	</para>
       </listitem>
      </varlistentry>
     </variablelist>
    </para>
   </refsect3>
   
   
   <refsect3 id="R3-SQL-CHECK-4">
    <title>
     CHECK clause
    </title>
    <para>
     SQL92 specifies some additional capabilities for CHECK in either
table or column constraints.
    </para>
<!--
Constraints associated with domains do not need to be mentioned here,
even though it is the case that a domain constraint may possibly
affect a column or a table.
- Thomas 1998-08-16
    <para>
     A CHECK constraint is either a table constraint, a column
     constraint or a domain constraint.
    </para> 
-->
    <para>
     table constraint definition:
     <synopsis>
[ CONSTRAINT <replaceable>constraint_name</replaceable> ] CHECK ( VALUE <replaceable>condition</replaceable> ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>

    <para>
     column constraint definition:
    <synopsis>
[ CONSTRAINT <replaceable>constraint_name</replaceable> ] CHECK ( VALUE <replaceable>condition</replaceable> ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    </synopsis>
    </para>
<!--
   <para>
     domain constraint definition: 
    </para>
    <synopsis>
     [ CONSTRAINT name] 
      CHECK ( VALUE condition ) 
      [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
      [ [ NOT ] DEFERRABLE ]
    </synopsis>
    <para>
     CHECK domain constraints can be defined in either
     a CREATE DOMAIN statement or an ALTER DOMAIN statement:
    </para>
    <programlisting>
CREATE DOMAIN duration AS SMALLINT 
    CONSTRAINT minutes CHECK (VALUE IN (90,120,180,240)); 

ALTER DOMAIN cities 
    ADD CONSTRAINT new_city CHECK (VALUE LIKE 'L%');
   </programlisting>
-->
   </refsect3>

   <refsect3 id="R3-SQL-PRIMARYKEY-1">
    <title>
     PRIMARY KEY clause
    </title>
    <para>
     SQL92 specifies some additional capabilities for PRIMARY KEY:
    </para>
    <para>
     Table Constraint definition:
     <synopsis>
[ CONSTRAINT <replaceable>constraint_name</replaceable> ] PRIMARY KEY ( <replaceable>column</replaceable> [, ... ] ) 
    [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>
    <para>
     Column Constraint definition: 
     <synopsis>
[ CONSTRAINT <replaceable>constraint_name</replaceable> ] PRIMARY KEY 
    [ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
    [ [ NOT ] DEFERRABLE ]
     </synopsis>
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-INHERITANCE-1">
    <title>
     Inheritance
    </title>
    <para>
     Multiple inheritance via the INHERITS clause is a
     <productname>Postgres</productname> language extension.
     SQL99 (but not SQL92) defines single inheritance using a different
     syntax and different semantics.  SQL99-style inheritance is not yet
     supported by <productname>Postgres</productname>.
    </para>
   </refsect3>

   <refsect3 id="R3-SQL-OBJECTIDS-1">
    <title>
     Object IDs
    </title>
    <para>
     The <productname>Postgres</productname> concept of OIDs is not
     standard.  SQL99 (but not SQL92) has a notion of object ID, but
     the syntax and semantics are different --- SQL99 associates OIDs
     with individual values, not with rows.
    </para>
   </refsect3>
  </refsect2>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->
