<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_table.sgml,v 1.39 2001/01/05 06:34:16 tgl 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>
Creates a new table
</refpurpose>
</refnamediv>
<refsynopsisdiv>
<refsynopsisdivinfo>
<date>2001-01-04</date>
</refsynopsisdivinfo>
<synopsis>
CREATE [ TEMPORARY | TEMP ] TABLE <replaceable class="PARAMETER">table</replaceable> (
<replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable>
[ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT <replaceable class="PARAMETER">value</replaceable> ]
[<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">
<title>
Inputs
</title>
<para>
<variablelist>
<varlistentry>
<term>TEMPORARY</term>
<listitem>
<para>
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
while the temporary table exists.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">table</replaceable></term>
<listitem>
<para>
The name of the 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 list 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.
</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>
<varlistentry>
<term><computeroutput>
ERROR: DEFAULT: type mismatched
</computeroutput></term>
<listitem>
<para>
If data type of default value doesn't match the
column definition's data type.
</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 table
into the current data base. 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-TITLE" 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>
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, Postgres reports an error. Postgres automatically
allows the created table to inherit functions on tables above it in
the inheritance hierarchy. Inheritance of functions is done according
to the conventions of the Common Lisp Object System (CLOS).
</para>
<para>
Each new table <replaceable class="PARAMETER">table</replaceable>
is automatically created as a type. Therefore, one or more rows
from the table are automatically a type and can be used in
<xref linkend="sql-altertable" endterm="sql-altertable-title">
or other <command>CREATE TABLE</command> statements.
</para>
<para>
The new table is created as a heap with no initial data.
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-DEFAULTCLAUSE-1">
<title id="R1-SQL-DEFAULTCLAUSE-1-TITLE">
DEFAULT Clause
</title>
<para>
<synopsis>
DEFAULT <replaceable class="PARAMETER">value</replaceable>
</synopsis>
</para>
<refsect2 id="R2-SQL-DEFAULTCLAUSE-1">
<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>
</para>
</refsect2>
<refsect2 id="R2-SQL-DEFAULTCLAUSE-2">
<title>
Outputs
</title>
<para>
None.
</para>
</refsect2>
<refsect2 id="R2-SQL-DEFAULTCLAUSE-3">
<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>See CURRENT_USER function</simpara>
</listitem>
</varlistentry>
<varlistentry>
<term>SYSTEM_USER</term>
<listitem>
<simpara>Not implemented</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>
</refsect2>
<refsect2 id="R2-SQL-DEFAULTCLAUSE-4">
<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>
<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>
</para>
</refsect2>
</refsect1>
<refsect1 id="R1-SQL-COLUMNCONSTRAINT-1">
<title id="R1-SQL-COLUMNCONSTRAINT-1-TITLE">
Column CONSTRAINT Clause
</title>
<para>
<synopsis>
[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] { [
NULL | NOT NULL ] | UNIQUE | PRIMARY KEY | CHECK <replaceable
class="parameter">constraint</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">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>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 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>
</para>
</refsect2>
<refsect2 id="R2-SQL-COLUMNCONSTRAINT-2">
<title>
Description
</title>
<para>
The optional constraint clauses specify 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. Multiple attributes may be referenced within a single
constraint. The use of PRIMARY KEY as a table constraint is mutually
incompatible with PRIMARY KEY as a column constraint.
</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 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>
</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 being a named constraint:
<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">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>
</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 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>
</para>
</refsect3>
</refsect2>
<refsect2 id="R2-SQL-CHECK-1">
<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><replaceable class="parameter">name</replaceable></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>
</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">table_column</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 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">
<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><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.
</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">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>CONSTRAINT <replaceable class="PARAMETER">name</replaceable></term>
<listitem>
<para>
An arbitrary name for the constraint.
</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 a 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 CONSTRAINT Clause
</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>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>
<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 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>
</refsect2>
<refsect2 id="R2-SQL-UNIQUECLAUSE-4">
<title>
UNIQUE Constraint
</title>
<para>
<synopsis>
[ CONSTRAINT <replaceable class="parameter">name</replaceable> ] UNIQUE ( <replaceable class="parameter">column</replaceable> [, ...] )
</synopsis>
</para>
<refsect3>
<title>Inputs</title>
<variablelist>
<varlistentry>
<term>CONSTRAINT <replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
An arbitrary name given to a constraint.
</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>
Define a UNIQUE table constraint for the table distributors:
<programlisting>
CREATE TABLE distributors (
did DECIMAL(3),
name VARCHAR(40),
UNIQUE(name)
);
</programlisting>
</para>
</refsect3>
</refsect2>
<refsect2 id="R2-SQL-PRIMARYKEY-4">
<title>
PRIMARY KEY Constraint
</title>
<para>
<synopsis>
[ CONSTRAINT <replaceable class="PARAMETER">name</replaceable> ] PRIMARY KEY ( <replaceable class="PARAMETER">column</replaceable> [, ...] )
</synopsis>
</para>
<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>
<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">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 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-5">
<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>
<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 column 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 <> '')
);
</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, the
default option, ON COMMIT DELETE ROWS, is assumed.
</para>
<para>
To create a temporary table:
<programlisting>
CREATE TEMPORARY TABLE actors (
id DECIMAL(3),
name VARCHAR(40),
CONSTRAINT actor_id CHECK (id < 150)
) ON COMMIT DELETE ROWS;
</programlisting>
</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>name</replaceable> ] UNIQUE ( <replaceable>column</replaceable> [, ...] )
[ { INITIALLY DEFERRED | INITIALLY IMMEDIATE } ]
[ [ NOT ] DEFERRABLE ]
</synopsis>
</para>
<para>
Column Constraint definition:
<synopsis>
[ CONSTRAINT <replaceable>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 is
included for symmetry with the NOT NULL clause. Since it is the
default for any column, its presence is simply noise.
<synopsis>
[ CONSTRAINT <replaceable>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>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 base 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>name</replaceable> ] CHECK <replaceable>constraint</replaceable>
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
</synopsis>
</para>
<para>
Table constraint definition:
<synopsis>
[ CONSTRAINT <replaceable>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>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 only at the end of the transaction. This
is the default
</para>
</listitem>
</varlistentry>
<varlistentry>
<term>INITIALLY DEFERRED</term>
<listitem>
<para>
Check constraint after each statement.
</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>name</replaceable> ] CHECK ( VALUE <replaceable>condition</replaceable> )
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
</synopsis>
</para>
<para>
column constraint definition:
<synopsis>
[ CONSTRAINT <replaceable>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>name</replaceable> ] PRIMARY KEY ( <replaceable>column</replaceable> [, ...] )
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
</synopsis>
</para>
<para>
Column Constraint definition:
<synopsis>
[ CONSTRAINT <replaceable>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>
</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:
-->