CREATE TABLE 中文man页面-51CTO.COM

NAME

CREATE TABLE - 定义一个新表

SYNOPSIS

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
    { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
    | table_constraint
    | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }  [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
  CHECK (expression) |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
  PRIMARY KEY ( column_name [, ... ] ) |
  CHECK ( expression ) |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

DESCRIPTION 描述

CREATE TABLE 将在当前数据库创建一个新的，初始为空的表。该表将由发出此命令的用户所有。

如果给出了模式名（比如，CREATE TABLE myschema.mytable ...），那么表是在指定模式中创建的。否则它在当前模式中创建。临时表存在于一个特殊的模式里，因此创建临时表的时候不能给出模式名。表名字必需和同一模式中其他表，序列，索引或者视图相区别。

CREATE TABLE 还自动创建一个数据类型，该数据类型代表对应该表一行的复合类型。因此，表不能和同模式中的现有数据类型同名。

一个表的字段数不能超过 1600。（实际上，真正的限制比这低，因为还有元组长度的约束）。

可选的约束子句声明约束（或者测试），新行或者更新的行必须满足这些约束才能成功插入或更新。约束是一个它是一个 SQL 对象，它以多种方式协助我们协助我们在表上定义有效的数值集合。

定义约束又两种方法：表约束和列约束。一个列约束是作为一个列定义的一部分定义的。而表约束并不和某个列绑在一起，它可以作用于多于一个列上。每个列约束也可以写成表约束；如果某个约束只影响一个列，那么列约束只是符号上的简洁方式而已。

PARAMETERS 参数

TEMPORARY 或 TEMP

如果声明了此参数，则该表创建为临时表。临时表在会话结束时自动删除，或者是（可选）在当前事务的结尾（参阅下面的 ON COMMIT）。现有同名永久表在临时表存在期间在本会话过程中是不可见的，除非它们是用模式修饰的名字引用的。任何在临时表上创建的索引也都会自动删除。

我们可以选择在 TEMPORARY 或 TEMP 前面放上 GLOBAL 或者 LOCAL。这样对 PostgreSQL 没有任何区别，可以参阅 Compatibility [create_table(7)]。

table_name

要创建的表的名字（可以用模式修饰）。

column_name

在新表中要创建的字段名字。

data_type

该字段的数据类型。它可以包括数组说明符。

DEFAULT

DEFAULT 子句给它所出现的字段一个缺省数值。该数值可以是任何不含变量的表达式（不允许使用子查询和对本表中的其它字段的交叉引用）。缺省表达式的数据类型必须和字段类型匹配。

缺省表达式将被用于任何未声明该字段数值的插入操作。如果字段上没有缺省值，那么缺省是 NULL。

LIKE 子句声明一个表，新表自动从这个表里面继承所有字段名，他们的数据类型，以及非空约束。

和 INHERITS 不同，新表与继承过来的表之间在创建动作完毕之后是完全无关的。插入新表的数据不会在父表中表现出来。

字段缺省表达式只有在声明了 INCLUDING DEFAULTS 之后才会继承过来。缺省是排除缺省表达式。

INHERITS ( parent_table [, ... ] )

可选的 INHERITS 子句声明一列表，这个新表自动从这列表中继承所有字段。如果在多于一个父表中存在同名的字段，那么就会报告一个错误，除非这些字段的数据类型在每个父表里都是匹配的。如果没有冲突，那么重复的字段在新表中融合成一个字段。如果新表的字段名列表中包括和继承的字段同名的，那么它的数据类型也必须和上面一样与继承字段匹配，并且这些字段定义会融合成一个。不过，同名的继承和新字段声明可以声明不同的约束：所有的继承过来的约束以及声明的约束都融合到一起，并且全部应用于新表。如果新表为该字段明确的声明了一个缺省数值，那么此缺省数值覆盖任何来自继承字段声明的缺省值。否则，任何为该字段声明了缺省数值的父表都必须声明相同的缺省，否则就会报告一个错误。

WITH OIDS

WITHOUT OIDS

这个可选的子句声明新表中的行是否应该拥有赋予它们的 OID （对象标识）。缺省是有 OID。（如果新表从任何有 OID 的表继承而来，那么就算这条命令说了 WITHOUT OIDS，也会强制 WITH OIDS。）

声明 WITHOUT OIDS 允许用户禁止为行或者表生成 OID。这么做对大表是值得的，因为这样可以减少 OID 消耗并且推迟 32 位 OID 计数器的消耗。一旦该计数器重叠，那么就不能再假设 OID 的唯一，这样它的实用性就大打折扣。声明 WITHOUT OIDS 还会减少在磁盘上存储每行的空间，每行减少 4 字节，因此也可以改进性能。

CONSTRAINT constraint_name

列或表约束的可选名字。如果没有声明，则由系统生成一个名字。

NOT NULL

字段不允许包含 NULL 数值。

NULL

该字段允许包含 NULL 数值。这是缺省。

这个子句的存在只是为和那些非标准 SQL 数据库兼容。我们不建议在新应用中使用它。

UNIQUE (column constraint)

UNIQUE ( column_name [, ... ] ) (table constraint)

UNIQUE 声明一个规则，表示一个表里的一个或者多个独立的字段组合的分组只能包含唯一的数值。表的唯一约束的行为和列约束的一样，只不过多了跨多行的能力。

对于唯一约束的用途而言，系统认为 NULL 数值是不相等的。

每个唯一表约束都必须命名一个字段的集合，该集合必须和其它唯一约束命名字段集合或者该表定义的主键约束不同。（否则就只是同样的约束写了两次。）

PRIMARY KEY (column constraint)

PRIMARY KEY ( column_name [, ... ] ) (table constraint)

主键约束表明表中的一个或者一些字段只能包含唯一（不重复）非 NULL 的数值。从技术上讲，PRIMARY KEY 只是 UNIQUE 和 NOT NULL 的组合，不过把一套字段标识为主键同时也体现了模式设计的元数据，因为主键意味着其它表可以拿这套字段用做行的唯一标识。

一个表只能声明一个主键，不管是作为字段约束还是表约束。

主键约束应该定义在同个表上的一个与其它唯一约束所定义的不同的字段集合上。

CHECK (expression)

CHECK 约束声明一个生成布尔结果的子句，一次插入或者更新操作若想成功则里面的新行或者被更新的行必须满足这个条件。声明为字段约束的检查约束应该只引用该字段的数值，而在表约束里出现的表达式可以引用多个字段。

目前，CHECK 表达式不能包含子查询也不能引用除当前行字段之外的变量。

REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint)

FOREIGN KEY ( column [, ... ] )

这些子句声明一个外键约束，外键约束声明一个由新表中一列或者多列组成的组应该只包含匹配引用的表 reftable 中对应引用的字段 refcolumn 中的数值。如果省略 refcolumn，则使用 reftable 的主键。被引用字段必须是被引用表中的唯一字段或者主键。

向这些字段插入的数值将使用给出的匹配类型与参考表中的参考列中的数值进行匹配。有三种匹配类型：MATCH FULL， MATCH PARTIAL，和 MATCH SIMPLE，它也是缺省匹配类型。 MATCH FULL 将不允许一个多字段外键的字段为 NULL，除非所有外键字段都为 NULL。 MATCH SIMPLE 允许某些外键字段为 NULL 而外键的其它部分不是 NULL。MATCH PARTIAL 还没实现。

另外，当被参考字段中的数据改变的时候，那么将对本表的字段中的数据执行某种操作。 ON DELETE 子句声明当被参考表中的被参考行将被删除的时候要执行的操作。类似，ON UPDATE 子句声明被参考表中被参考字段更新为新值的时候要执行的动作。如果该行被更新，但被参考的字段实际上没有变化，那么就不会有任何动作。下面是每个子句的可能的动作：

NO ACTION: 生成一个错误，表明删除或者更新将产生一个违反外键约束的动作。它是缺省动作。
RESTRICT: 和 NO ACTION 一样，只是动作不可推迟，即使约束剩下的部分是可以推迟的也马上发生。
CASCADE: 删除任何引用了被删除行的行，或者分别把引用行的字段值更新为被参考字段的新数值。
SET NULL: 把引用行数值设置为 NULL。
SET DEFAULT: 把引用列的数值设置为它们的缺省值。

如果主键字段经常更新，那么我们给 REFERENCES 字段增加一个索引可能是合适的，这样与 REFERENCES 字段相关联的 NO ACTION 和 CASCADE 动作可以更有效地执行。

DEFERRABLE

NOT DEFERRABLE

这两个关键字设置该约束是否可推迟。一个不可推迟的约束将在每条命令之后马上检查。可以推迟的约束检查可以推迟到事务结尾（使用 SET CONSTRAINTS [set_constraints(7)] 命令）。缺省是 NOT DEFERRABLE。目前只有外键约束接受这个子句。所有其它约束类型都是不可推迟的。

INITIALLY IMMEDIATE

INITIALLY DEFERRED

如果约束是可推迟的，那么这个子句声明检查约束的缺省时间。如果约束是 INITIALLY IMMEDIATE，那么每条语句之后就检查它。这个是缺省。如果约束是 INITIALLY DEFERRED，那么只有在事务结尾才检查它。约束检查的时间可以用 SET CONSTRAINTS [set_constraints(7)] 命令修改。

ON COMMIT

我们可以用 ON COMMIT 控制临时表在事务块结尾的行为。这三个选项是：

PRESERVE ROWS: 在事务结尾不发生任何特定的动作。这是缺省行为。
DELETE ROWS: 临时表的所有行在每次事务结尾都被删除。实际上，在每次提交的时候都自动 truncate(7) 。
DROP: 在当前事务块的结尾，临时表将被删除。

NOTES 注意

*

如果一个应用使用了 OID 标识表中的特定行，那么我们建议在该表的 oid 字段上创建一个唯一约束，以确保该表的 OID 即使在计数器重叠之后也是唯一的。如果你需要一个整个数据库范围的唯一标识，那么就要避免假设 OID 是跨表唯一的，你可以用 tableoid 和行 OID 的组合来实现这个目的。（将来的 PostgreSQL 很可能为每个表使用独立的 OID 计数器，因此包括 tableoid 组成数据库范围内的唯一标识将是必须的，而不是可选的。）

: 提示: 对那些没有主键的表，我们不建议使用 WITHOUT OIDS，因为如果既没有 OID 又没有唯一数据键字，那么就很难标识特定的行。

*

PostgreSQL 自动为每个唯一约束和主键约束创建一个索引以确保唯一性。因此，我们不必为主键字段创建明确的索引。（参阅 CREATE INDEX [create_index(7)]获取更多信息。）

*

唯一约束和主键在目前的实现里是不能继承的。这样，如果把继承和唯一约束组合在一起会导致无法运转。

EXAMPLES 例子

创建表 films 和 distributors：

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

CREATE TABLE distributors (
     did    integer PRIMARY KEY DEFAULT nextval('serial'),
     name   varchar(40) NOT NULL CHECK (name <> '')
);

创建一个带有 2 维数组的表：

CREATE TABLE array (
    vector  int[][]
);

为表 films 定义一个唯一表约束。唯一表约束可以在表的一个或多个字段上定义：

CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT production UNIQUE(date_prod)
);

定义一个检查列约束：

CREATE TABLE distributors (
    did     integer CHECK (did > 100),
    name    varchar(40)
);

定义一个检查表约束：

CREATE TABLE distributors (
    did     integer,
    name    varchar(40)
    CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);

为表 films 定义一个主键表约束。主键表约束可以定义在表上的一个或多个字段。

CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);

为表 distributors 定义一个主键约束。下面两个例子是等效的，第一个例子使用了表约束语法，第二个使用了列约束表示法。

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    PRIMARY KEY(did)
);

CREATE TABLE distributors (
    did     integer PRIMARY KEY,
    name    varchar(40)
);

下面这个例子给字段 name 赋予了一个文本常量缺省值，并且将字段 did 的缺省值安排为通过选择序列对象的下一个值生成。 modtime 的缺省值将是该行插入的时候的时间。

CREATE TABLE distributors (
    name      varchar(40) DEFAULT 'Luso Films',
    did       integer DEFAULT nextval('distributors_serial'),
    modtime   timestamp DEFAULT current_timestamp
);

在表 distributors 上定义两个 NOT NULL 列约束，其中之一明确给出了名字：

CREATE TABLE distributors (
    did     integer CONSTRAINT no_null NOT NULL,
    name    varchar(40) NOT NULL
);

为 name 字段定义一个唯一约束：

CREATE TABLE distributors (
    did     integer,
    name    varchar(40) UNIQUE
);

上面的和下面这样作为一个表约束声明是一样的：

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name)
);

COMPATIBILITY 兼容性

CREATE TABLE 遵循 SQL92 和 SQL99 的一个子集，一些例外情况在下面列出。

TEMPORARY TABLES 临时表

尽管 CREATE TEMPORARY TABLE 的语法和 SQL 标准的类似，但是效果是不同的。在标准里，临时表只是定义一次并且自动存在（从空内容开始）于任何需要它们的会话中。 PostgreSQL 要求每个会话为它们使用的每个临时表发出它们自己的 CREATE TEMPORARY TABLE 命令。这样就允许不同的会话将相同的临时表名字用于不同的目的，而标准的实现方法则把一个临时表名字约束为具有相同的表结构。

标准定义的临时表的行为被广泛地忽略了。PostgreSQL 在这方面上地行为类似于许多其它 SQL 数据库

标准中在全局和局部地临时表之间的区别在 PostgreSQL 里不存在，因为这种区别取决于模块的概念，而 PostgreSQL 没有这个概念。出于兼容考虑，PostgreSQL 将接受临时表声明中的 GLOBAL 和 LOCAL 关键字，但是他们没有作用。

临时表的 ON COMMIT 子句也类似于 SQL 标准，但是有些区别。如果忽略了 ON COMMIT 子句，SQL 声明缺省的行为是 ON COMMIT DELETE ROWS。但是 PostgreSQL 里的缺省行为是 ON COMMIT PRESERVE ROWS。在 SQL 里不存在 ON COMMIT DROP。

COLUMN CHECK CONSTRAINTS 字段检查约束

SQL 标准说 CHECK 字段约束只能引用他们施用的字段；只有 CHECK 表约束才能引用多个字段。PostgreSQL 并不强制这个限制；它把字段和表约束看作相同的东西。

NULL ``CONSTRAINT'' NULL约束

NULL "约束"（实际上不是约束）是 PostgreSQL 对 SQL 标准的扩展，包括它是为了和其它一些数据库系统兼容（以及为了和 NOT NULL 约束对称）。因为它是任何字段的缺省，所以它的出现只是噪音而已。

INHERITANCE 继承

通过 INHERITS 子句的多重继承是 PostgreSQL 语言的扩展。 SQL99（但不包括 SQL92）使用不同的语法和语义定义了单继承。 SQL99 风格的继承还没有在 PostgreSQL 中实现。

OBJECT IDS 对象ID

PostgreSQL 的 OID 的概念不标准。

ZERO-COLUMN TABLES 零行表

PostgreSQL 允许创建没有字段的表（比如，CREATE TABLE foo();）。这是对 SQL 标准的扩展，标准不允许存在零字段表。零字段表本身没什么用，但是禁止他们会给 ALTER TABLE DROP COLUMN带来很奇怪的情况，所以，这个时候忽视标准的限制好想很清楚。

NAME

CREATE TABLE - define a new table

SYNOPSIS

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
    { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
    | table_constraint
    | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] }  [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | UNIQUE | PRIMARY KEY |
  CHECK (expression) |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
  PRIMARY KEY ( column_name [, ... ] ) |
  CHECK ( expression ) |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

DESCRIPTION

CREATE TABLE will create a new, initially empty table in the current database. The table will be owned by the user issuing the command.

If a schema name is given (for example, CREATE TABLE myschema.mytable ...) then the table is created in the specified schema. Otherwise it is created in the current schema. Temporary tables exist in a special schema, so a schema name may not be given when creating a temporary table. The table name must be distinct from the name of any other table, sequence, index, or view in the same schema.

CREATE TABLE also automatically creates a data type that represents the composite type corresponding to one row of the table. Therefore, tables cannot have the same name as any existing data type in the same schema.

A table cannot have more than 1600 columns. (In practice, the effective limit is lower because of tuple-length constraints).

The optional constraint clauses specify constraints (or tests) that new or updated rows must satisfy for an insert or update operation to succeed. A constraint is an SQL object that helps define the set of valid values in the table in various ways.

There are two ways to define constraints: table constraints and column constraints. A column constraint is defined as part of a column definition. A table constraint definition is not tied to a particular column, and it can encompass more than one column. Every column constraint can also be written as a table constraint; a column constraint is only a notational convenience if the constraint only affects one column.

PARAMETERS

TEMPORARY or TEMP

If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This makes no difference in PostgreSQL, but see Compatibility [create_table(7)].

table_name

The name (optionally schema-qualified) of the table to be created.

column_name

The name of a column to be created in the new table.

data_type

The data type of the column. This may include array specifiers.

DEFAULT

The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed). The data type of the default expression must match the data type of the column.

The default expression will be used in any insert operation that does not specify a value for the column. If there is no default for a column, then the default is null.

LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ]

The LIKE clause specifies a table from which the new table automatically inherits all column names, their data types, and not-null constraints.

Unlike INHERITS, the new table and inherited table are complete decoupled after creation has been completed. Data inserted into the new table will not be reflected into the parent table.

Default expressions for the inherited column definitions will only be included if INCLUDING DEFAULTS is specified. The default is to exclude default expressions.

INHERITS ( parent_table [, ... ] )

The optional INHERITS clause specifies a list of tables from which the new table automatically inherits all columns. If the same column name exists in more than one parent table, an error is reported unless the data types of the columns match in each of the parent tables. If there is no conflict, then the duplicate columns are merged to form a single column in the new table. If the column name list of the new table contains a column that is also inherited, the data type must likewise match the inherited column(s), and the column definitions are merged into one. However, inherited and new column declarations of the same name need not specify identical constraints: all constraints provided from any declaration are merged together and all are applied to the new table. If the new table explicitly specifies a default value for the column, this default overrides any defaults from inherited declarations of the column. Otherwise, any parents that specify default values for the column must all specify the same default, or an error will be reported.

WITH OIDS

WITHOUT OIDS

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

Specifying WITHOUT OIDS allows the user to suppress generation of OIDs for rows of a table. This may be worthwhile for large tables, since it will reduce OID consumption and thereby postpone wraparound of the 32-bit OID counter. Once the counter wraps around, uniqueness of OIDs can no longer be assumed, which considerably reduces their usefulness. Specifying WITHOUT OIDS also reduces the space required to store the table on disk by 4 bytes per row of the table, thereby improving performance.

CONSTRAINT constraint_name

An optional name for a column or table constraint. If not specified, the system generates a name.

NOT NULL

The column is not allowed to contain null values.

NULL

The column is allowed to contain null values. This is the default.

This clause is only available for compatibility with non-standard SQL databases. Its use is discouraged in new applications.

UNIQUE (column constraint)

UNIQUE ( column_name [, ... ] ) (table constraint)

The UNIQUE constraint specifies 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.

For the purpose of a unique constraint, null values are not considered equal.

Each unique table constraint must name a set of columns that is different from the set of columns named by any other unique or primary key constraint defined for the table. (Otherwise it would just be the same constraint listed twice.)

PRIMARY KEY (column constraint)

PRIMARY KEY ( column_name [, ... ] ) (table constraint)

The primary key constraint specifies that a column or columns of a table may contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables may rely on this set of columns as a unique identifier for rows.

Only one primary key can be specified for a table, whether as a column constraint or a table constraint.

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.

CHECK (expression)

The CHECK clause specifies an expression producing a Boolean result which new or updated rows must satisfy for an insert or update operation to succeed. A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint may reference multiple columns.

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row.

REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint)

FOREIGN KEY ( column [, ... ] )

Theses clauses specify a foreign key constraint, which specifies that a group of one or more columns of the new table must only contain values which match against values in the referenced column(s) refcolumn of the referenced table reftable. If refcolumn is omitted, the primary key of the reftable is used. The referenced columns must be the columns of a unique or primary key constraint in the referenced table.

A value inserted into these columns is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE, which is also the default. MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null. MATCH SIMPLE allows some foreign key columns to be null while other parts of the foreign key are not null. MATCH PARTIAL is not yet implemented.

In addition, when the data in the referenced columns is changed, certain actions are performed on the data in this table's columns. The ON DELETE clause specifies the action to perform when a referenced row in the referenced table is being deleted. Likewise, the ON UPDATE clause specifies the action to perform 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 actually changed, no action is done. There are the following possible actions for each clause:

NO ACTION: Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the default action.
RESTRICT: Same as NO ACTION.
CASCADE: Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the referenced column, respectively.
SET NULL: Set the referencing column values to null.
SET DEFAULT: Set the referencing column values to their default value.

If primary key column is updated frequently, it may be wise to add an index to the foreign key column so that NO ACTION and CASCADE actions associated with the foreign key column can be more efficiently performed.

DEFERRABLE

NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable may be postponed until the end of the transaction (using the SET CONSTRAINTS [set_constraints(7)] command). NOT DEFERRABLE is the default. Only foreign key constraints currently accept this clause. All other constraint types are not deferrable.

INITIALLY IMMEDIATE

INITIALLY DEFERRED

If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS [set_constraints(7)] command.

ON COMMIT

The behavior of temporary tables at the end of a transaction block can be controlled using ON COMMIT. The three options are:

PRESERVE ROWS: No special action is taken at the ends of transactions. This is the default behavior.
DELETE ROWS: All rows in the temporary table will be deleted at the end of each transaction block. Essentially, an automatic truncate(7) is done at each commit.
DROP: The temporary table will be dropped at the end of the current transaction block.

NOTES

*

Whenever an application makes use of OIDs to identify specific rows of a table, it is recommended to create a unique constraint on the oid column of that table, to ensure that OIDs in the table will indeed uniquely identify rows even after counter wraparound. Avoid assuming that OIDs are unique across tables; if you need a database-wide unique identifier, use the combination of tableoid and row OID for the purpose. (It is likely that future PostgreSQL releases will use a separate OID counter for each table, so that it will be necessary, not optional, to include tableoid to have a unique identifier database-wide.)

: Tip: The use of WITHOUT OIDS is not recommended for tables with no primary key, since without either an OID or a unique data key, it is difficult to identify specific rows.

*

PostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce the uniqueness. Thus, it is not necessary to create an explicit index for primary key columns. (See CREATE INDEX [create_index(7)] for more information.)

*

Unique constraints and primary keys are not inherited in the current implementation. This makes the combination of inheritance and unique constraints rather dysfunctional.

EXAMPLES

Create table films and table distributors:

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);

CREATE TABLE distributors (
     did    integer 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         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT production UNIQUE(date_prod)
);

Define a check column constraint:

CREATE TABLE distributors (
    did     integer CHECK (did > 100),
    name    varchar(40)
);

Define a check table constraint:

CREATE TABLE distributors (
    did     integer,
    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         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);

Define a primary key constraint for table distributors. The following two examples are equivalent, the first using the table constraint syntax, the second the column constraint notation.

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    PRIMARY KEY(did)
);

CREATE TABLE distributors (
    did     integer PRIMARY KEY,
    name    varchar(40)
);

This assigns a literal constant default value for the column name, arranges for the default value of column did to be generated by selecting the next value of a sequence object, and makes the default value of modtime be the time at which the row is inserted.

CREATE TABLE distributors (
    name      varchar(40) DEFAULT 'Luso Films',
    did       integer DEFAULT nextval('distributors_serial'),
    modtime   timestamp DEFAULT current_timestamp
);

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

CREATE TABLE distributors (
    did     integer CONSTRAINT no_null NOT NULL,
    name    varchar(40) NOT NULL
);

Define a unique constraint for the name column:

CREATE TABLE distributors (
    did     integer,
    name    varchar(40) UNIQUE
);

The above is equivalent to the following specified as a table constraint:

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name)
);

COMPATIBILITY

The CREATE TABLE command conforms to SQL92 and to a subset of SQL99, with exceptions listed below.

TEMPORARY TABLES

Although the syntax of CREATE TEMPORARY TABLE resembles that of the SQL standard, the effect is not the same. In the standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them. PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different purposes, whereas the standard's approach constrains all instances of a given temporary table name to have the same table structure.

The standard's definition of the behavior of temporary tables is widely ignored. PostgreSQL's behavior on this point is similar to that of several other SQL databases.

The standard's distinction between global and local temporary tables is not in PostgreSQL, since that distinction depends on the concept of modules, which PostgreSQL does not have. For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL keywords in a temporary table declaration, but they have no effect.

The ON COMMIT clause for temporary tables also resembles the SQL standard, but has some differences. If the ON COMMIT clause is omitted, SQL specifies that the default behavior is ON COMMIT DELETE ROWS. However, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS. The ON COMMIT DROP option does not exist in SQL.

COLUMN CHECK CONSTRAINTS

The SQL standard says that CHECK column constraints may only refer to the column they apply to; only CHECK table constraints may refer to multiple columns. PostgreSQL does not enforce this restriction; it treats column and table check constraints alike.

NULL ``CONSTRAINT''

The NULL ``constraint'' (actually a non-constraint) is a PostgreSQL extension to the SQL standard that is included for compatibility with some other database systems (and for symmetry with the NOT NULL constraint). Since it is the default for any column, its presence is simply noise.

INHERITANCE

Multiple inheritance via the INHERITS clause is a PostgreSQL language extension. SQL99 (but not SQL92) defines single inheritance using a different syntax and different semantics. SQL99-style inheritance is not yet supported by PostgreSQL.

OBJECT IDS

The PostgreSQL concept of OIDs is not standard.

ZERO-COLUMN TABLES

PostgreSQL allows a table of no columns to be created (for example, CREATE TABLE foo();). This is an extension from the SQL standard, which does not allow zero-column tables. Zero-column tables are not in themselves very useful, but disallowing them creates odd special cases for ALTER TABLE DROP COLUMN, so it seems cleaner to ignore this spec restriction.

CREATE TABLE 中文man页面

NAME

SYNOPSIS

DESCRIPTION 描述

PARAMETERS 参数

NOTES 注意

EXAMPLES 例子

COMPATIBILITY 兼容性

TEMPORARY TABLES 临时表

COLUMN CHECK CONSTRAINTS 字段检查约束

NULL ``CONSTRAINT'' NULL约束

INHERITANCE 继承

OBJECT IDS 对象ID

ZERO-COLUMN TABLES 零行表

SEE ALSO 参见

NAME

SYNOPSIS

DESCRIPTION

PARAMETERS

NOTES

EXAMPLES

COMPATIBILITY

TEMPORARY TABLES

COLUMN CHECK CONSTRAINTS

NULL ``CONSTRAINT''

INHERITANCE

OBJECT IDS

ZERO-COLUMN TABLES

SEE ALSO