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 將在當前數(shù)據(jù)庫創(chuàng)建一個新的， 初始為空的表。該表將由發(fā)出此命令的用戶所有。

 如果給出了模式名（比如，CREATE TABLE myschema.mytable ...）， 那么表是在指定模式中創(chuàng)建的。否則它在當前模式中創(chuàng)建。臨時表存在于一個特殊的模式里， 因此創(chuàng)建臨時表的時候不能給出模式名。表名字必需和同一模式中其他表，序列，索引或者視圖相區(qū)別。

CREATE TABLE 還自動創(chuàng)建一個數(shù)據(jù)類型， 該數(shù)據(jù)類型代表對應該表一行的復合類型。 因此，表不能和同模式中的現(xiàn)有數(shù)據(jù)類型同名。

 一個表的字段數(shù)不能超過 1600。（實際上，真正的限制比這低，因為還有元組長度的約束）。

 可選的約束子句聲明約束（或者測試），新行或者更新的行必須滿足這些約束才能成功插入或更新。 約束是一個它是一個 SQL 對象，它以多種方式協(xié)助我們協(xié)助我們在表上定義有效的數(shù)值集合。

 定義約束又兩種方法：表約束和列約束。一個列約束是作為一個列定義的一部分定義的。 而表約束并不和某個列綁在一起， 它可以作用于多于一個列上。每個列約束也可以寫成表約束； 如果某個約束只影響一個列，那么列約束只是符號上的簡潔方式而已。  

PARAMETERS 參數(shù)

TEMPORARY 或 TEMP

 如果聲明了此參數(shù)，則該表創(chuàng)建為臨時表。臨時表在會話結束時自動刪除， 或者是（可選）在當前事務的結尾（參閱下面的 ON COMMIT）。 現(xiàn)有同名永久表在臨時表存在期間在本會話過程中是不可見的， 除非它們是用模式修飾的名字引用的。 任何在臨時表上創(chuàng)建的索引也都會自動刪除。

 我們可以選擇在 TEMPORARY 或 TEMP 前面放上 GLOBAL 或者 LOCAL。 這樣對 PostgreSQL 沒有任何區(qū)別，可以參閱 Compatibility [create_table(7)]。

table_name

 要創(chuàng)建的表的名字（可以用模式修飾）。

column_name

 在新表中要創(chuàng)建的字段名字。

data_type

 該字段的數(shù)據(jù)類型。它可以包括數(shù)組說明符。

DEFAULT

DEFAULT 子句給它所出現(xiàn)的字段一個缺省數(shù)值。 該數(shù)值可以是任何不含變量的表達式（不允許使用子查詢和對本表中的其它字段的交叉引用）。 缺省表達式的數(shù)據(jù)類型必須和字段類型匹配。

 缺省表達式將被用于任何未聲明該字段數(shù)值的插入操作。 如果字段上沒有缺省值，那么缺省是 NULL。

LIKE 子句聲明一個表，新表自動從這個表里面繼承所有字段名， 他們的數(shù)據(jù)類型，以及非空約束。

 和 INHERITS 不同，新表與繼承過來的表之間在創(chuàng)建動作完畢之后是完全無關的。 插入新表的數(shù)據(jù)不會在父表中表現(xiàn)出來。
 
 字段缺省表達式只有在聲明了 INCLUDING DEFAULTS 之后才會繼承過來。 缺省是排除缺省表達式。

INHERITS ( parent_table [, ... ] )

 可選的 INHERITS 子句聲明一列表，這個新表自動從這列表中繼承所有字段。 如果在多于一個父表中存在同名的字段，那么就會報告一個錯誤，除非這些字段的數(shù)據(jù)類型在每個父表里都是匹配的。 如果沒有沖突，那么重復的字段在新表中融合成一個字段。 如果新表的字段名列表中包括和繼承的字段同名的，那么它的數(shù)據(jù)類型也必須和上面一樣與繼承字段匹配，并且這些字段定義會融合成一個。 不過，同名的繼承和新字段聲明可以聲明不同的約束：所有的繼承過來的約束以及聲明的約束都融合到一起，并且全部應用于新表。 如果新表為該字段明確的聲明了一個缺省數(shù)值，那么此缺省數(shù)值覆蓋任何來自繼承字段聲明的缺省值。 否則，任何為該字段聲明了缺省數(shù)值的父表都必須聲明相同的缺省，否則就會報告一個錯誤。

WITH OIDS

WITHOUT OIDS

 這個可選的子句聲明新表中的行是否應該擁有賦予它們的 OID （對象標識）。 缺省是有 OID。（如果新表從任何有 OID 的表繼承而來，那么就算這條命令說了 WITHOUT OIDS， 也會強制 WITH OIDS。）

 聲明 WITHOUT OIDS 允許用戶禁止為行或者表生成 OID。 這么做對大表是值得的，因為這樣可以減少 OID 消耗并且推遲 32 位 OID 計數(shù)器的消耗。 一旦該計數(shù)器重疊，那么就不能再假設 OID 的唯一，這樣它的實用性就大打折扣。 聲明 WITHOUT OIDS 還會減少在磁盤上存儲每行的空間，每行減少 4 字節(jié)，因此也可以改進性能。

CONSTRAINT constraint_name

 列或表約束的可選名字。如果沒有聲明，則由系統(tǒng)生成一個名字。

NOT NULL

 字段不允許包含 NULL 數(shù)值。

NULL

 該字段允許包含 NULL 數(shù)值。這是缺省。

 這個子句的存在只是為和那些非標準 SQL 數(shù)據(jù)庫兼容。 我們不建議在新應用中使用它。

UNIQUE (column constraint)

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

UNIQUE 聲明一個規(guī)則，表示一個表里的一個或者多個獨立的字段組合的分組只能包含唯一的數(shù)值。 表的唯一約束的行為和列約束的一樣，只不過多了跨多行的能力。

 對于唯一約束的用途而言，系統(tǒng)認為 NULL 數(shù)值是不相等的。

 每個唯一表約束都必須命名一個字段的集合，該集合必須和其它唯一約束命名字段集合或者該表定義的主鍵約束不同。 （否則就只是同樣的約束寫了兩次。）

PRIMARY KEY (column constraint)

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

 主鍵約束表明表中的一個或者一些字段只能包含唯一（不重復）非 NULL 的數(shù)值。 從技術上講，PRIMARY KEY 只是 UNIQUE 和 NOT NULL 的組合，不過把一套字段標識為主鍵同時也體現(xiàn)了模式設計的元數(shù)據(jù)， 因為主鍵意味著其它表可以拿這套字段用做行的唯一標識。

 一個表只能聲明一個主鍵，不管是作為字段約束還是表約束。

 主鍵約束應該定義在同個表上的一個與其它唯一約束所定義的不同的字段集合上。

CHECK (expression)

CHECK 約束聲明一個生成布爾結果的子句， 一次插入或者更新操作若想成功則里面的新行或者被更新的行必須滿足這個條件。 聲明為字段約束的檢查約束應該只引用該字段的數(shù)值，而在表約束里出現(xiàn)的表達式可以引用多個字段。

 目前，CHECK 表達式不能包含子查詢也不能引用除當前行字段之外的變量。

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

FOREIGN KEY ( column [, ... ] )

 這些子句聲明一個外鍵約束，外鍵約束聲明一個由新表中一列或者多列組成的組應該只包含匹配引用的表 reftable 中對應引用的字段 refcolumn 中的數(shù)值。 如果省略 refcolumn， 則使用 reftable 的主鍵。 被引用字段必須是被引用表中的唯一字段或者主鍵。

 向這些字段插入的數(shù)值將使用給出的匹配類型與參考表中的參考列中的數(shù)值進行匹配。 有三種匹配類型：MATCH FULL， MATCH PARTIAL，和 MATCH SIMPLE，它也是缺省匹配類型。 MATCH FULL 將不允許一個多字段外鍵的字段為 NULL，除非所有外鍵字段都為 NULL。 MATCH SIMPLE 允許某些外鍵字段為 NULL 而外鍵的其它部分不是 NULL。MATCH PARTIAL  還沒實現(xiàn)。

 另外，當被參考字段中的數(shù)據(jù)改變的時候，那么將對本表的字段中的數(shù)據(jù)執(zhí)行某種操作。 ON DELETE 子句聲明當被參考表中的被參考行將被刪除的時候要執(zhí)行的操作。 類似，ON UPDATE 子句聲明被參考表中被參考字段更新為新值的時候要執(zhí)行的動作。 如果該行被更新，但被參考的字段實際上沒有變化，那么就不會有任何動作。 下面是每個子句的可能的動作：

NO ACTION

 生成一個錯誤，表明刪除或者更新將產(chǎn)生一個違反外鍵約束的動作。 它是缺省動作。

RESTRICT

 和 NO ACTION 一樣，只是動作不可推遲， 即使約束剩下的部分是可以推遲的也馬上發(fā)生。

CASCADE

 刪除任何引用了被刪除行的行，或者分別把引用行的字段值更新為被參考字段的新數(shù)值。

SET NULL

 把引用行數(shù)值設置為 NULL。

SET DEFAULT

 把引用列的數(shù)值設置為它們的缺省值。

 如果主鍵字段經(jīng)常更新，那么我們給 REFERENCES  字段增加一個索引可能是合適的，這樣與 REFERENCES 字段相關聯(lián)的 NO ACTION  和 CASCADE 動作可以更有效地執(zhí)行。

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

 在事務結尾不發(fā)生任何特定的動作。這是缺省行為。

DELETE ROWS

 臨時表的所有行在每次事務結尾都被刪除。實際上，在每次提交的時候都自動 truncate(7)  。

DROP

 在當前事務塊的結尾，臨時表將被刪除。

NOTES 注意

*

 如果一個應用使用了 OID 標識表中的特定行，那么我們建議在該表的 oid 字段上創(chuàng)建一個唯一約束，以確保該表的 OID 即使在計數(shù)器重疊之后也是唯一的。如果你需要一個整個數(shù)據(jù)庫范圍的唯一標識， 那么就要避免假設 OID 是跨表唯一的，你可以用 tableoid 和行 OID 的組合來實現(xiàn)這個目的。 （將來的 PostgreSQL 很可能為每個表使用獨立的 OID 計數(shù)器， 因此包括 tableoid 組成數(shù)據(jù)庫范圍內(nèi)的唯一標識將是必須的，而不是可選的。）

提示: 對那些沒有主鍵的表，我們不建議使用 WITHOUT OIDS， 因為如果既沒有 OID 又沒有唯一數(shù)據(jù)鍵字，那么就很難標識特定的行。

*

PostgreSQL 自動為每個唯一約束和主鍵約束創(chuàng)建一個索引以確保唯一性。 因此，我們不必為主鍵字段創(chuàng)建明確的索引。（參閱 CREATE INDEX [create_index(7)]獲取更多信息。）

*

 唯一約束和主鍵在目前的實現(xiàn)里是不能繼承的。 這樣，如果把繼承和唯一約束組合在一起會導致無法運轉。

EXAMPLES 例子

 創(chuàng)建表 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 <> '')
);

 創(chuàng)建一個帶有 2 維數(shù)組的表：

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 標準的類似， 但是效果是不同的。在標準里，臨時表只是定義一次并且自動存在（從空內(nèi)容開始）于任何需要它們的會話中。 PostgreSQL 要求每個會話為它們使用的每個臨時表發(fā)出它們自己的 CREATE TEMPORARY TABLE 命令。 這樣就允許不同的會話將相同的臨時表名字用于不同的目的，而標準的實現(xiàn)方法則把一個臨時表名字約束為具有相同的表結構。

 標準定義的臨時表的行為被廣泛地忽略了。PostgreSQL  在這方面上地行為類似于許多其它 SQL 數(shù)據(jù)庫

 標準中在全局和局部地臨時表之間的區(qū)別在 PostgreSQL  里不存在，因為這種區(qū)別取決于模塊的概念，而 PostgreSQL  沒有這個概念。出于兼容考慮，PostgreSQL 將接受臨時表聲明中的 GLOBAL 和 LOCAL 關鍵字， 但是他們沒有作用。

 臨時表的 ON COMMIT 子句也類似于 SQL 標準， 但是有些區(qū)別。如果忽略了 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 標準的擴展， 包括它是為了和其它一些數(shù)據(jù)庫系統(tǒng)兼容（以及為了和 NOT NULL 約束對稱）。因為它是任何字段的缺省，所以它的出現(xiàn)只是噪音而已。  

INHERITANCE 繼承

 通過 INHERITS 子句的多重繼承是 PostgreSQL 語言的擴展。 SQL99（但不包括 SQL92）使用不同的語法和語義定義了單繼承。 SQL99 風格的繼承還沒有在 PostgreSQL 中實現(xiàn)。  

OBJECT IDS 對象ID

PostgreSQL 的 OID 的概念不標準。  

ZERO-COLUMN TABLES 零行表

PostgreSQL 允許創(chuàng)建沒有字段的表 （比如，CREATE TABLE foo();）。這是對 SQL 標準的擴展， 標準不允許存在零字段表。零字段表本身沒什么用，但是禁止他們會給 ALTER TABLE DROP COLUMN帶來很奇怪的情況，所以，這個時候忽視標準的限制好想很清楚。  

SEE ALSO 參見

ALTER TABLE [alter_table(7)], DROP TABLE [drop_table(l)]  

#p#

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.  

SEE ALSO

ALTER TABLE [alter_table(7)], DROP TABLE [drop_table(l)]