CREATE TABLE() SQL Commands CREATE TABLE()NAME
CREATE TABLE - Creates a new table
SYNOPSIS
CREATE [ TEMPORARY | TEMP ] TABLE table_name (
{ column_name type [ column_constraint [ ... ] ]
| table_constraint } [, ... ]
) [ INHERITS ( inherited_table [, ... ] ) ]
where column_constraint can be:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY | DEFAULT value | CHECK (condition) |
REFERENCES table [ ( column ) ] [ MATCH FULL | MATCH PARTIAL ]
[ ON DELETE action ] [ ON UPDATE action ]
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
}
and table_constraint can be:
[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
PRIMARY KEY ( column_name [, ... ] ) |
CHECK ( condition ) |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES table [ ( column [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ]
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
}
INPUTS
TEMPORARY or TEMP
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 tem‐
porary table are automatically temporary as well.
table_name
The name of the new table to be created.
column_name
The name of a column to be created in the new table.
type The type of the column. This may include array specifiers.
Refer to the PostgreSQL User's Guide for further information
about data types and arrays.
inherited_table
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, Postgres reports an
error. Postgres automatically allows the created table to
inherit functions on tables above it in the inheritance hierar‐
chy.
constraint_name
An optional name for a column or table constraint. If not speci‐
fied, the system generates a name.
value A default value for a column. See the DEFAULT clause for more
information.
condition
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 defini‐
tion should reference that column's value only, while a condi‐
tion appearing as a table constraint may reference multiple col‐
umns.
table The name of an existing table to be referenced by a foreign key
constraint.
column 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.
action A keyword indicating the action to take when a foreign key con‐
straint is violated.
OUTPUTS
CREATE Message returned if table is successfully created.
ERROR Message returned if table creation failed. This is usually
accompanied by some descriptive text, such as: ERROR: Relation
'table' already exists , which occurs at runtime if the table
specified already exists in the database.
DESCRIPTION
CREATE TABLE will enter a new, initially empty table into the current
database. The table will be "owned" by the user issuing the command.
Each type 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 DEFAULT Clause [create_table(l)].
Note: Consistent array dimensions within an attribute are not
enforced. This will likely change in a future release.
CREATE TABLE 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.
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).
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.
DEFAULT CLAUSE
DEFAULT value
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.
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.
USAGE
CREATE TABLE distributors (
name VARCHAR(40) DEFAULT 'luso films',
did INTEGER DEFAULT NEXTVAL('distributors_serial'),
modtime TIMESTAMP DEFAULT now()
);
The above assigns a literal constant default value for the column name,
and arranges for the default value of column did to be generated by
selecting the next value of a sequence object. The default value of
modtime will be the time at which the row is inserted.
It is worth remarking that
modtime TIMESTAMP DEFAULT 'now'
would produce a result that is probably not the intended one: the
string 'now' will be coerced to a timestamp value immediately, and so
the default value of modtime will always be the time of table creation.
This difficulty is avoided by specifying the default value as a func‐
tion call.
COLUMN CONSTRAINTS
[ CONSTRAINT constraint_name ] {
NULL | NOT NULL | UNIQUE | PRIMARY KEY | CHECK condition |
REFERENCES reftable [ ( refcolumn ) ]
[ MATCH matchtype ]
[ ON DELETE action ]
[ ON UPDATE action ]
[ [ NOT ] DEFERRABLE ]
[ INITIALLY checktime ] }
INPUTS
constraint_name
An arbitrary name given to a constraint clause.
NULL The column is allowed to contain NULL values. This is the
default.
NOT NULL
The column is not allowed to contain NULL values. This is
equivalent to the column constraint CHECK (column NOT NULL).
UNIQUE The column must have unique values. In Postgres this is enforced
by automatic creation of a unique index on the column.
PRIMARY KEY
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.
condition
An arbitrary boolean-valued constraint condition.
DESCRIPTION
The optional constraint clauses specify constraints or tests which new
or updated rows must satisfy for an insert or update operation to suc‐
ceed.
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.
There are two ways to define integrity constraints: table constraints,
covered later, and column constraints, covered here.
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:
PRIMARY KEY
REFERENCES
UNIQUE
CHECK
NOT NULL
NOT NULL CONSTRAINT
[ CONSTRAINT name ] NOT NULL
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.
OUTPUTS
status
ERROR: ExecAppend: Fail to add null value in not null attribute
"column".
This error occurs at runtime if one tries to insert a
null value into a column which has a NOT NULL constraint.
DESCRIPTION
USAGE
Define two NOT NULL column constraints on the table distributors, one
of which is explicitly given a name:
CREATE TABLE distributors (
did DECIMAL(3) CONSTRAINT no_null NOT NULL,
name VARCHAR(40) NOT NULL
);
UNIQUE CONSTRAINT
[ CONSTRAINT constraint_name ] UNIQUE
INPUTS
constraint_name
An arbitrary name given to a constraint clause.
OUTPUTS
status
ERROR: Cannot insert a duplicate key into a unique index.
This error occurs at runtime if one tries to insert a
duplicate value into a column.
DESCRIPTION
The UNIQUE constraint specifies a rule that a group of one or more dis‐
tinct columns of a table may contain only unique values.
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 SQL92
definition, but is a more sensible convention. See the section on com‐
patibility for more details.)
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.
Note: Postgres automatically creates a unique index for each
UNIQUE constraint, to assure data integrity. See CREATE INDEX
for more information.
USAGE
Defines a UNIQUE constraint for the name column:
CREATE TABLE distributors (
did DECIMAL(3),
name VARCHAR(40) UNIQUE
);
which is equivalent to the following specified as a table constraint:
CREATE TABLE distributors (
did DECIMAL(3),
name VARCHAR(40),
UNIQUE(name)
);
THE CHECK CONSTRAINT
[ CONSTRAINT constraint_name ] CHECK ( condition )
INPUTS
constraint_name
An arbitrary name given to a constraint clause.
condition
Any valid conditional expression evaluating to a boolean result.
OUTPUTS
status
ERROR: ExecAppend: rejected due to CHECK constraint "con‐
straint_name".
This error occurs at runtime if one tries to insert an
illegal value into a column subject to a CHECK con‐
straint.
DESCRIPTION
The CHECK constraint specifies a generic restriction on allowed values
within a column. The CHECK constraint is also allowed as a table con‐
straint.
CHECK specifies a general boolean expression involving one or more col‐
umns of a table. A new row will be rejected if the boolean expression
evaluates to FALSE when applied to the row's values.
Currently, CHECK expressions cannot contain sub-selects nor refer to
variables other than fields of the current row.
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. Postgres does not enforce this restriction. It
treats column and table CHECK constraints alike.
PRIMARY KEY CONSTRAINT
[ CONSTRAINT constraint_name ] PRIMARY KEY
INPUTS
constraint_name
An arbitrary name given to a constraint clause.
OUTPUTS
ERROR: Cannot insert a duplicate key into a unique index.
This occurs at runtime if one tries to insert a duplicate value
into a column subject to a PRIMARY KEY constraint.
DESCRIPTION
The PRIMARY KEY column constraint specifies that a column of a table
may contain only unique (non-duplicate), non-NULL values. The defini‐
tion of the specified column does not have to include an explicit NOT
NULL constraint to be included in a PRIMARY KEY constraint.
Only one PRIMARY KEY can be specified for a table, whether as a column
constraint or a table constraint.
NOTES
Postgres automatically creates a unique index to assure data integrity
(see CREATE INDEX statement).
The PRIMARY KEY constraint should name a set of columns that is differ‐
ent 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, Post‐
gres does not specifically disallow this.
REFERENCES CONSTRAINT
[ CONSTRAINT constraint_name ] REFERENCES reftable [ ( refcolumn ) ]
[ MATCH matchtype ]
[ ON DELETE action ]
[ ON UPDATE action ]
[ [ NOT ] DEFERRABLE ]
[ INITIALLY checktime ]
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.
INPUTS
constraint_name
An arbitrary name given to a constraint clause.
reftable
The table that contains the data to check against.
refcolumn
The column in reftable to check the data against. If this is not
specified, the PRIMARY KEY of the reftable is used.
MATCH matchtype
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 sup‐
ported.
ON DELETE action
The action to do when a referenced row in the referenced table
is being deleted. There are the following actions.
NO ACTION
Produce error if foreign key violated. This is the
default.
RESTRICT
Same as NO ACTION.
CASCADE
Delete any rows referencing the deleted row.
SET NULL
Set the referencing column values to NULL.
SET DEFAULT
Set the referencing column values to their default value.
ON UPDATE action
The action to do when a referenced column in the referenced ta‐
ble 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.
NO ACTION
Produce error if foreign key violated. This is the
default.
RESTRICT
Same as NO ACTION.
CASCADE
Update the value of the referencing column to the new
value of the referenced column.
SET NULL
Set the referencing column values to NULL.
SET DEFAULT
Set the referencing column values to their default value.
[ NOT ] DEFERRABLE
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.
INITIALLY checktime
checktime has two possible values which specify the default time
to check the constraint.
DEFERRED
Check constraint only at the end of the transaction.
IMMEDIATE
Check constraint after each statement. This is the
default.
OUTPUTS
status
ERROR: name referential integrity violation - key referenced
from
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.
DESCRIPTION
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.
A value added to this column is matched against the values of the ref‐
erenced 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.
NOTES
Currently Postgres 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 Postgres does not
enforce this.
TABLE CONSTRAINTS
[ CONSTRAINT name ] { PRIMARY KEY | UNIQUE } ( column [, ... ] )
[ CONSTRAINT name ] CHECK ( constraint )
[ CONSTRAINT name ] FOREIGN KEY ( column [, ... ] )
REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH matchtype ]
[ ON DELETE action ]
[ ON UPDATE action ]
[ [ NOT ] DEFERRABLE ]
[ INITIALLY checktime ]
INPUTS
constraint_name
An arbitrary name given to a constraint clause.
column [, ... ]
The column name(s) for which to define a unique index and, for
PRIMARY KEY, a NOT NULL constraint.
CHECK ( constraint )
A boolean expression to be evaluated as the constraint.
OUTPUTS
The possible outputs for the table constraint clause are the same as
for the corresponding portions of the column constraint clause.
DESCRIPTION
A table constraint is an integrity constraint defined on one or more
columns of a table. The four variations of "Table Constraint" are:
UNIQUE
CHECK
PRIMARY KEY
FOREIGN KEY
UNIQUE CONSTRAINT
[ CONSTRAINT constraint_name ] UNIQUE ( column [, ... ] )
INPUTS
constraint_name
An arbitrary name given to a constraint clause.
column A name of a column in a table.
OUTPUTS
status
ERROR: Cannot insert a duplicate key into a unique index
This error occurs at runtime if one tries to insert a
duplicate value into a column.
DESCRIPTION
The UNIQUE constraint specifies a rule that a group of one or more dis‐
tinct columns of a table may contain only unique values. The behavior
of the UNIQUE table constraint is the same as that for column con‐
straints, with the additional capability to span multiple columns.
See the section on the UNIQUE column constraint for more details.
USAGE
Prevent duplicate rows in the table distributors:
CREATE TABLE distributors (
did DECIMAL(3),
name VARCHAR(40),
UNIQUE(did,name)
);
PRIMARY KEY CONSTRAINT
[ CONSTRAINT constraint_name ] PRIMARY KEY ( column [, ... ] )
INPUTS
constraint_name
An arbitrary name given to a constraint clause.
column [, ... ]
The names of one or more columns in the table.
OUTPUTS
status
ERROR: Cannot insert a duplicate key into a unique index.
This occurs at run-time if one tries to insert a dupli‐
cate value into a column subject to a PRIMARY KEY con‐
straint.
DESCRIPTION
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.
The PRIMARY KEY table constraint is similar to that for column con‐
straints, with the additional capability of encompassing multiple col‐
umns.
Refer to the section on the PRIMARY KEY column constraint for more
information.
REFERENCES CONSTRAINT
[ CONSTRAINT constraint_name ] FOREIGN KEY ( column [, ... ] )
REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH matchtype ]
[ ON DELETE action ]
[ ON UPDATE action ]
[ [ NOT ] DEFERRABLE ]
[ INITIALLY checktime ]
The REFERENCES constraint specifies a rule that a column value or set
of column values is checked against the values in another table.
INPUTS
constraint_name
An arbitrary name given to a constraint clause.
column [, ... ]
The names of one or more columns in the table.
reftable
The table that contains the data to check against.
referenced column [, ... ]
One or more columns in the reftable to check the data against.
If this is not specified, the PRIMARY KEY of the reftable is
used.
MATCH matchtype
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 sup‐
ported.
ON DELETE action
The action to do when a referenced row in the referenced table
is being deleted. There are the following actions.
NO ACTION
Produce error if foreign key violated. This is the
default.
RESTRICT
Same as NO ACTION.
CASCADE
Delete any rows referencing the deleted row.
SET NULL
Set the referencing column values to NULL.
SET DEFAULT
Set the referencing column values to their default value.
ON UPDATE action
The action to do when a referenced column in the referenced ta‐
ble 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.
NO ACTION
Produce error if foreign key violated. This is the
default.
RESTRICT
Disallow update of row being referenced.
CASCADE
Update the value of the referencing column to the new
value of the referenced column.
SET NULL
Set the referencing column values to NULL.
SET DEFAULT
Set the referencing column values to their default value.
[ NOT ] DEFERRABLE
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.
INITIALLY checktime
checktime has two possible values which specify the default time
to check the constraint.
IMMEDIATE
Check constraint after each statement. This is the
default.
DEFERRED
Check constraint only at the end of the transaction.
OUTPUTS
status
ERROR: name referential integrity violation - key referenced
from
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.
DESCRIPTION
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.
The FOREIGN KEY table constraint is similar to that for column con‐
straints, with the additional capability of encompassing multiple col‐
umns.
Refer to the section on the FOREIGN KEY column constraint for more
information.
USAGE
Create table films and table distributors:
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
);
CREATE TABLE distributors (
did DECIMAL(3) PRIMARY KEY DEFAULT NEXTVAL('serial'),
name VARCHAR(40) NOT NULL CHECK (name <> '')
);
Create a table with a 2-dimensional array:
CREATE TABLE array (
vector INT[][]
);
Define a UNIQUE table constraint for the table films. UNIQUE table
constraints can be defined on one or more columns of the table:
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)
);
Define a CHECK column constraint:
CREATE TABLE distributors (
did DECIMAL(3) CHECK (did > 100),
name VARCHAR(40)
);
Define a CHECK table constraint:
CREATE TABLE distributors (
did DECIMAL(3),
name VARCHAR(40)
CONSTRAINT con1 CHECK (did > 100 AND name > '')
);
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:
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)
);
Defines a PRIMARY KEY column constraint for table distributors. PRI‐
MARY KEY column constraints can only be defined on one column of the
table (the following two examples are equivalent):
CREATE TABLE distributors (
did DECIMAL(3),
name CHAR VARYING(40),
PRIMARY KEY(did)
);
CREATE TABLE distributors (
did DECIMAL(3) PRIMARY KEY,
name VARCHAR(40)
);
COMPATIBILITY
SQL92
In addition to the locally visible temporary table, SQL92 also defines
a CREATE GLOBAL TEMPORARY TABLE statement, and optionally an ON COMMIT
clause:
CREATE GLOBAL TEMPORARY TABLE table ( column type [
DEFAULT value ] [ CONSTRAINT column_constraint ] [, ... ] )
[ CONSTRAINT table_constraint ] [ ON COMMIT { DELETE | PRESERVE } ROWS ]
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.
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, Postgres' behavior
is always like ON COMMIT PRESERVE ROWS.
UNIQUE CLAUSE
SQL92 specifies some additional capabilities for UNIQUE:
Table Constraint definition:
[ CONSTRAINT constraint_name ] UNIQUE ( column [, ... ] )
[ { INITIALLY DEFERRED | INITIALLY IMMEDIATE } ]
[ [ NOT ] DEFERRABLE ]
Column Constraint definition:
[ CONSTRAINT constraint_name ] UNIQUE
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
NULL CLAUSE
The NULL "constraint" (actually a non-constraint) is a Postgres exten‐
sion 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.
[ CONSTRAINT constraint_name ] NULL
NOT NULL CLAUSE
SQL92 specifies some additional capabilities for NOT NULL:
[ CONSTRAINT constraint_name ] NOT NULL
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
CONSTRAINT CLAUSE
SQL92 specifies some additional capabilities for constraints, and also
defines assertions and domain constraints.
Note: Postgres does not yet support either domains or asser‐
tions.
An assertion is a special type of integrity constraint and shares the
same namespace as other constraints. However, an assertion is not nec‐
essarily dependent on one particular table as constraints are, so
SQL-92 provides the CREATE ASSERTION statement as an alternate method
for defining a constraint:
CREATE ASSERTION name CHECK ( condition )
Domain constraints are defined by CREATE DOMAIN or ALTER DOMAIN state‐
ments:
Domain constraint:
[ CONSTRAINT constraint_name ] CHECK constraint
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
Table constraint definition:
[ CONSTRAINT constraint_name ] { PRIMARY KEY ( column, ... ) | FOREIGN KEY constraint | UNIQUE constraint | CHECK constraint }
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
Column constraint definition:
[ CONSTRAINT constraint_name ] { NOT NULL | PRIMARY KEY | FOREIGN KEY constraint | UNIQUE | CHECK constraint }
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
A CONSTRAINT definition may contain one deferment attribute clause
and/or one initial constraint mode clause, in any order.
NOT DEFERRABLE
The constraint must be checked at the end of each statement.
SET CONSTRAINTS ALL DEFERRED will have no effect on this type of
constraint.
DEFERRABLE
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.
Note: SET CONSTRAINTS changes the foreign key constraint mode
only for the current transaction.
INITIALLY IMMEDIATE
Check constraint after each statement. This is the default.
INITIALLY DEFERRED
Check constraint only at the end of the transaction.
CHECK CLAUSE
SQL92 specifies some additional capabilities for CHECK in either table
or column constraints.
table constraint definition:
[ CONSTRAINT constraint_name ] CHECK ( VALUE condition )
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
column constraint definition:
[ CONSTRAINT constraint_name ] CHECK ( VALUE condition )
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
PRIMARY KEY CLAUSE
SQL92 specifies some additional capabilities for PRIMARY KEY:
Table Constraint definition:
[ CONSTRAINT constraint_name ] PRIMARY KEY ( column [, ... ] )
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
Column Constraint definition:
[ CONSTRAINT constraint_name ] PRIMARY KEY
[ {INITIALLY DEFERRED | INITIALLY IMMEDIATE} ]
[ [ NOT ] DEFERRABLE ]
INHERITANCE
Multiple inheritance via the INHERITS clause is a Postgres language
extension. SQL99 (but not SQL92) defines single inheritance using a
different syntax and different semantics. SQL99-style inheritance is
not yet supported by Postgres.
SQL - Language Statements 29 March 2001 CREATE TABLE()
