建立表 - PolarDB

建立一張新表。

文法

CREATE [ GLOBAL TEMPORARY ] TABLE table_name (
  { column_name data_type [ DEFAULT default_expr ]
  [ column_constraint [ ... ] ] | table_constraint } [, ...]
  )
  [ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
  [ TABLESPACE tablespace ]

其中column_constraint是下列選項之一：

  [ CONSTRAINT constraint_name ]
  { NOT NULL |
    NULL |
    UNIQUE [ USING INDEX TABLESPACE tablespace ] |
    PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
    CHECK (expression) |
    REFERENCES reftable [ ( refcolumn ) ]
      [ ON DELETE action ] }
  [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED |
    INITIALLY IMMEDIATE ]

table constraint是下列選項之一：

  [ CONSTRAINT constraint_name ]
  { UNIQUE ( column_name [, ...] )
      [ USING INDEX TABLESPACE tablespace ] |
    PRIMARY KEY ( column_name [, ...] )
      [ USING INDEX TABLESPACE tablespace ] |
    CHECK ( expression ) |
    FOREIGN KEY ( column_name [, ...] )
        REFERENCES reftable [ ( refcolumn [, ...] ) ]
      [ ON DELETE action ] }
  [ DEFERRABLE | NOT DEFERRABLE ]
  [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

參數

參數名稱	描述
GLOBAL TEMPORARY	如果指定此參數，則會建立一張暫存資料表。在會話結束時會自動刪除暫存資料表，您也可以在當前事務結束時進行此操作（詳細資料請參考ON COMMIT參數）。說明如果存在相同名稱的永久表，則當暫存資料表存在的時候，永久表對於當前會話來說是不可見的，除非使用模式限定的名稱來引用這些表。除此之外，暫存資料表在建立它的會話範圍之外也是不可見的（全域暫存資料表在這個方面與Oracle不相容）。任何在暫存資料表上建立的索引也是臨時存在的。
table name	新建立表的名稱。說明可以採用模式限定的方式引用。
column name	新表中要建立列的名稱。
data type	列的資料類型。資料類型可能包括數組定義。
DEFAULT default_expr	`DEFAULT`子句為出現在列定義中的列分配一個預設值。這個值可以是任意運算式（但不允許使用子查詢和當前表中對其他列的引用）。預設運算式的資料類型必須匹配列的資料類型。當在插入操作中沒有為列指定值的時候，您可以使用預設運算式。如果沒有為列指定預設值。那麼預設值為空白。
CONSTRAINT constraint_name	為列或者資料表條件約束所指定的名稱（這個名稱可選）。如果沒有指定的話，那麼系統會為這個約束自動產生一個名稱。
NOT NULL	不允許列中包含空值。
PRIMARY KEY - 資料行層級條件約束	主鍵約束指定表上的一列或者多列資料只包含唯一（不可重複），非空的值。主鍵僅僅是唯一性限制式和非空約束的組合。但是將一個列的集合標識為主鍵提供了關於模式設計的中繼資料，因為主鍵會使其它表將這個列集合做為記錄的唯一性標識。說明無論是做為資料行層級條件約束，還是表級約束，每個表只能指定一個主鍵。主鍵約束命名的列集合應該與同一表上為其他唯一性限制式命名的列集合不同。
PRIMARY KEY ( column_name [, ...] ) - 表級約束
CHECK (expression)	`CHECK`子句指定了一個產生布爾結果值的運算式。只有滿足這個布林運算式，插入或者更新記錄的操作才能成功。如果運算式的計算結果為true或者unkown，那麼表示滿足條件。任何無法滿足運算式的插入或者更新操作記錄都將產生一個錯誤異常，並且插入和更新操作不能改變資料庫的記錄值。指定為資料行層級條件約束的檢查約束只能訪問相關列的值，同時出現在表級約束的運算式可以引用多列值。說明 `CHECK`運算式不能包含子查詢或者引用非目前記錄列的變數。
REFERENCES reftable [ ( refcolumn ) ] [ ON DELETE action ] - 資料行層級條件約束	這些子句指定了一個外鍵約束，要求新建立表的相關列只能包含符合所參考資料表中的列的值。如果沒有使用`refcolumn`，那麼使用`reftable`的主鍵。被引用的列必須是被參考資料表中具有唯一性或者主鍵約束的列。除此之外，當所引用列的資料發生改變時，在這個表上相關列上也要執行相關的操作。當刪除參考資料表的記錄的時候，`ON DELETE`子句會指定要執行的操作。儘管約束可以是延遲的，但是引用操作不能延遲。針對每個子句，允許的操作如下： CASCADE：分別刪除任何引用被刪除記錄的記錄，或者將引用列上的值更新為被引用列的值。 SET NULL：將引用列的值設定為空白。說明如果被引用的列的值經常改變，在外鍵列上最好加上一個索引。這樣確保與外鍵列相關的引用操作能夠執行的更有效率。
FOREIGN KEY ( column [, ...] ) REFERENCES reftable [ ( refcolumn [, ...] ) ] [ ON DELETE action ] - 表級約束
NOT DEFERRABLE	用於控制約束是否可以延遲。每一條命令執行後，非延遲的約束就會立即進行檢查。可延遲的約束檢查只有在事務結束後才進行（使用`SET CONSTRAINTS`命令實現此操作）。 `NOT DEFERRABLE`是預設選項。當前只有外鍵約束使用這個子句。其他所有的約束類型是不可延遲的。
DEFERRABLE
INITIALLY IMMEDIATE	如果約束是可延遲的，這個子句指定了進行約束檢查的預設時間值。如果約束是INITIALLY IMMEDIATE，那麼在每條語句執行後，就會進行約束檢查。這是預設情況。如果約束是INITIALLY DEFERRED，那麼只有在事務結束後才能進行約束檢查。您可以使用`SET CONSTRAINT`命令來修改約束檢查時間。
INITIALLY DEFERRED
ON COMMIT	在事務塊結束時處理暫存資料表的操作可以由ON COMMIT來控制。包含以下兩個選項： PRESERVE ROWS：在事務結束時不進行特定的操作。這是預設系統行為。說明與Oracle不相容，Oracle在預設狀態下是DELETE ROWS。 DELETE ROWS：在每一個事務結束的時候，刪除暫存資料表中的所有記錄。實質上，就是在每一次提交後，自動執行一個`TRUNCATE`操作。

描述

CREATE TABLE命令在當前資料庫中建立一張新表，新建立的表初始狀態下沒有內容。執行CREATE TABLE命令的使用者是新建立表的所有者。

如果指定了模式名稱（例如，CREATE TABLE myschema.mytable…），那麼就在指定的模式中建立表，否則就是在當前模式下建立表。而暫存資料表只在一個特定的模式下存在，所以在建立暫存資料表的時候不需要指定模式名稱。表名不能和同一模式下其他表，序列，索引或者視圖的名稱一樣。

CREATE TABLE命令同時也會自動建立一個資料類型用來表示與表中一條記錄相對應的複合類型。因此，在同一模式中，表不能和已存在資料類型有相同的名稱。

一張表可以有最多1600個資料列（而在實際情況下，由於欄位長度的限制，真正有效限制值更低一些）。

可選的約束子句指定約束（或者是測試）條件。只有滿足這些約束（或者測試）條件，才能執行插入或更新記錄的操作。約束實際是一個SQL對象，以各種方式協助定義在表中有效值的集合。包含以下兩種方式定義約束：

資料行層級條件約束：做為列定義的一部分而進行定義的。
表級約束：不和特定的列相聯絡，包括多個列。

可以把每個資料行層級條件約束寫成一個表級約束：如果約束隻影響一列，那麼一個資料行層級條件約束只是一種簡單的表示方法。

說明為實現強制唯一性，PolarDB自動為每一個唯一性限制式或者主鍵約束建立一個索引。因此對於標識為主鍵的列，不用顯式地建立一個索引。

樣本

建立表dept和emp：

CREATE TABLE dept (
    deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
    dname           VARCHAR2(14),
    loc             VARCHAR2(13)
);
CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno)
);

為表dept定義一個唯一性表級約束。您可以在表中多列上定義唯一性表級約束。

CREATE TABLE dept (
    deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
    dname           VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
    loc             VARCHAR2(13)
);

定義一個列級檢查約束：

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno)
);

定義一個表級檢查約束：

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno),
    CONSTRAINT new_emp_ck CHECK (ename IS NOT NULL AND empno > 7000)
);

為表jobhist定義一個主鍵表級約束。您可以在表中多列上定義主鍵表級約束。

CREATE TABLE jobhist (
    empno           NUMBER(4) NOT NULL,
    startdate       DATE NOT NULL,
    enddate         DATE,
    job             VARCHAR2(9),
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2),
    chgdesc         VARCHAR2(80),
    CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate)
);

為列job分配一個文本常量預設值，並將記錄插入的日期設定為列hiredate的預設值。

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9) DEFAULT 'SALESMAN',
    mgr             NUMBER(4),
    hiredate        DATE DEFAULT SYSDATE,
    sal             NUMBER(7,2),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno)
);

在資料表空間diskvol1建立表dept：

CREATE TABLE dept (
    deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
    dname           VARCHAR2(14),
    loc             VARCHAR2(13)
) TABLESPACE diskvol1;