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 tempo
rary table exists. Any indexes created on a tempo
rary 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
Postgres automatically allows the created table to
inherit functions on tables above it in the inheri
tance hierarchy.
constraint_name
An optional name for a column or table constraint.
If not specified, 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 con
straint must be an expression producing a boolean
result. A condition appearing within a column def
inition should reference that column's value only,
while a condition appearing as a table constraint
may reference multiple columns.
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 for
eign key constraint 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)].
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 Com
mon Lisp Object System (CLOS).
A table can have no more than 1600 columns (in practice,
the effective limit is lower because of tuple-length con
straints). 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 cur
rent 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 opera
tion 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.
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 function
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
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, cov
ered 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 con
straints 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 dis
tributors, one of which is explicitly given a name:
CREATE TABLE distributors (
did DECIMAL(3) CONSTRAINT no_null 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 distinct 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 defini
tion, but is a more sensible convention. See the section
on compatibility 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
);
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 sub
ject to a CHECK constraint.
DESCRIPTION
The CHECK constraint specifies a generic restriction on
allowed values within a column. The CHECK constraint is
also allowed as a table constraint.
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.
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
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 definition 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 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, 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.
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 PAR
TIAL, and a default match type if none is speci
fied. MATCH FULL will not allow one column of a
multi-column foreign key to be NULL unless all for
eign 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.
ON DELETE action
The action to do when a referenced row in the ref
erenced table is being deleted. There are the fol
lowing 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 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 fol
lowing actions.
NO ACTION
Produce error if foreign key violated. This
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.
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 match
ing data.
NOTES
Currently Postgres only supports MATCH FULL and a default
match type. In addition, the referenced columns are sup
posed to be the columns of a UNIQUE constraint in the ref
erenced 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 con
straint.
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
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 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 addi
tional 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.
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 duplicate value into a column sub
ject to a PRIMARY KEY constraint.
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 constraints, with the additional capability of
encompassing multiple columns.
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 val
ues 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.
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 PAR
TIAL, and a default match type if none is speci
fied. MATCH FULL will not allow one column of a
multi-column foreign key to be NULL unless all for
eign 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.
ON DELETE action
The action to do when a referenced row in the ref
erenced table is being deleted. There are the fol
lowing 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 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 fol
lowing 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
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 constraints, with the additional capability of
encompassing multiple columns.
Refer to the section on the FOREIGN KEY column constraint
for more information.
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)
);
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 distrib
utors. PRIMARY 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 ] [, ... ] )
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 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.
[ 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 ]
SQL92 specifies some additional capabilities for con
straints, and also defines assertions and domain con
straints.
Note: Postgres does not yet support either domains
or assertions.
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 particu
lar table as constraints are, so SQL-92 provides the CRE
ATE ASSERTION statement as an alternate method for defin
ing a constraint:
CREATE ASSERTION name CHECK ( condition )
Domain constraints are defined by CREATE DOMAIN or ALTER
DOMAIN statements:
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
DEFERRABLE
This controls whether the constraint can be
deferred to the end of the transaction. If SET CON
STRAINTS ALL DEFERRED is used or the constraint is
set to INITIALLY DEFERRED, this will cause the for
eign key to be checked only at the end of the
transaction.
Note: SET CONSTRAINTS changes the foreign key con
straint 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 transac
tion.
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 ]
[ 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 seman
tics. SQL99-style inheritance is not yet supported by
Postgres.
Man(1) output converted with
man2html