创建表 - 云原生数据库 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;