CREATE TABLE

功能描述

在当前数据库中创建一个新的空白表，该表由命令执行者所有。

注意事项

• 如果在建表过程中数据库系统发生故障，系统恢复后可能无法自动清除之前已创建的、大小为0的磁盘文件。此种情况出现概率小，不影响数据库系统的正常运行。
• 使用JDBC时，支持通过PrepareStatement对DEFAULT值进行参数化设置。
• 被授予CREATE ANY TABLE权限的用户，可以在public模式和用户模式下创建表。
• 主键不支持前缀索引。

语法格式

CREATE [ [LOCAL | GLOBAL] TEMPORARY | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name 
    {({ column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset_name ] [ compress_mode ] [ COLLATE collation_name][AUTO_INCREMENT][COMMENT [=] 'string'] [ column_constraint [ ... ] ]
        | table_constraint
        | {INDEX | KEY} [ [schema_name.] index_name ] [ USING method ] ({column_name [(length)] | (expr) [ASC | DESC]}[,...]) [[COMMENT 'string' | USING method][...]]
        | LIKE source_table }
        [, ... ])}
    [ table_option ][[,...]|[...]]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS } ]
    [ TABLESPACE tablespace_name ];

CREATE [ [LOCAL | GLOBAL] TEMPORARY ] TABLE [IF NOT EXISTS] table_name LIKE source_table;

参数说明

• UNLOGGED

  如果指定该关键字，则创建的表为非日志表。

  • 在非日志表中写入的数据不会被写入到预写日志中，当执行操作系统重启、数据库重启、主备切换、切断电源操作或异常关机后，非日志表上的写入会被自动截断，会造成数据丢失的风险。非日志表中的内容也不会被复制到备服务器中。在非日志表中创建的索引也不会被自动记录。
  • 备服务器上非日志表无数据，且在备机查询非日志表时会报错。

  使用场景：非日志表无法保证数据的安全性，用户应该在确保数据已经做好备份的前提下使用非日志表，例如系统升级时进行数据的备份。

  故障处理：当异常关机等操作导致非日志表上的索引发生数据丢失时，用户应该对发生错误的索引进行重建。

• GLOBAL | LOCAL

  创建临时表时可以在TEMP或TEMPORARY前指定GLOBAL或LOCAL关键字。如果指定GLOBAL关键字，M-Compatibility会创建全局临时表，否则M-Compatibility会创建本地临时表。

• TEMPORARY

  如果指定该关键字，则创建的表为临时表。

  临时表分为全局临时表和本地临时表两种类型。创建临时表时如果指定GLOBAL关键字则为全局临时表，否则为本地临时表。

  全局临时表的元数据对所有会话可见，会话结束后元数据继续存在。会话与会话之间的用户数据、索引和统计信息相互隔离，每个会话只能看到和更改自己提交的数据。全局临时表有两种模式：一种是基于会话级别的(ON COMMIT PRESERVE ROWS), 当会话结束时自动清空用户数据；一种是基于事务级别的(ON COMMIT DELETE ROWS), 当执行commit或rollback时自动清空用户数据。建表时如果没有指定ON COMMIT选项，则缺省为会话级别。与本地临时表不同，全局临时表建表时可以指定非pg_temp_开头的schema。

  本地临时表只在当前会话可见，本会话结束后会自动删除。因此，在除当前会话连接的数据库节点故障时，仍然可以在当前会话上创建和使用临时表。由于临时表只在当前会话创建，对于涉及对临时表操作的DDL语句，会产生DDL失败的报错。因此，建议DDL语句中不要对临时表进行操作。

  

  • 本地临时表通过每个会话独立的以pg_temp开头的schema来保证只对当前会话可见，因此，不建议用户在日常操作中手动删除以pg_temp、pg_toast_temp开头的schema。
  • 如果建表时不指定TEMPORARY关键字，而指定表的schema为当前会话的pg_temp_开头的schema，则此表会被创建为临时表。
  • ALTER/DROP全局临时表和索引，如果其它会话正在使用它，禁止操作。
  • 全局临时表的DDL只会影响当前会话的用户数据和索引。例如truncate、reindex、analyze只对当前会话有效。
  • 全局临时表功能可以通过设置GUC参数max_active_global_temporary_table控制是否启用。如果max_active_global_temporary_table=0，关闭全局临时表功能。
  • 临时表只对当前会话可见，因此不支持与\parallel on并行执行一起使用。
  • 临时表不支持主备切换。
  • 全局临时表不响应自动清理，在长连接场景使用时尽量使用on commit delete rows的全局临时表，或定期手动执行vacuum，否则可能导致clog日志不回收。
  • 全局临时表不支持以下场景：
    • 不支持创建全局临时Sequence，各个会话的全局临时表使用共享的Sequence，只能保证唯一性，不保证连续性。
    • 不支持创建全局临时视图。
    • 不支持创建分区表。
    • 不支持创建Hash bucket表。
    • 不支持扩展统计信息。
• IF NOT EXISTS

  如果已经存在相同名称的表，不会报出错误，而会发出通知，告知通知此表已存在。

• table_name

  要创建的表名。

• column_name

  新表中要创建的字段名。

• constraint_name

  建表时指定的约束名称。

• index_name

  索引名。

  

  • 对于外键约束，constraint_name和index_name同时指定时，索引名为constraint_name。
  • 对于唯一键约束，constraint_name和index_name同时指定时，索引名为index_name。
• USING method

  指定创建索引的方法。

  取值范围请参见参数说明中的USING method。

  

  目前只支持索引方法为btree，但是不支持ustore表创建btree索引。

• ASC | DESC

  ASC表示指定按升序排序（默认）。DESC指定按降序排序。

• expression

  创建一个基于该表的一个或多个字段的表达式索引约束，必须写在圆括弧中。

• data_type

  字段的数据类型。

• compress_mode

  表字段的压缩选项。该选项指定表字段优先使用的压缩算法。行存表不支持压缩。当前M-Compatibility模式数据库下无法使用。

  取值范围：DICTIONARY

• {CHARACTER SET | CHAR SET | CHARSET} charset_name

  指定表字段的字符集。单独指定时会将字段的字符序设置为指定的字符集的默认字符序。

• COLLATE collation_name

  COLLATE子句指定列的排序规则（字符序）（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。排序规则可以使用“select * from pg_collation;”命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。支持的字符序具体请参见表级字符集和字符序

  

  • 仅字符类型支持指定字符集。指定为BINARY字符集或字符序实际是将字符类型转化为对应的二进制类型，若类型映射不存在则报错。当前仅有TEXT类型转化为BLOB的映射。
  • 字段字符集或字符序未显式指定时，若指定了表的默认字符集或字符序，字段字符集和字符序将从表上继承。
  • 除SQL_ASCII库外，其他字符集的数据库支持多字符集混用。
• LIKE source_table

  LIKE子句声明一个表，新表将从源表继承以下信息：

  • 字段名及其数据类型。
  • 非空约束、CHECK约束、主键约束、唯一键约束、索引、默认值以及ON UPDATE属性。
  • 表、字段、索引以及约束上的注释。
  • 分区信息。
  • 表的存储参数以及字段的存储参数。

  新表与源表之间在创建动作完毕之后是完全无关的。在源表做的任何修改都不会在新表中产生影响，并且在扫描源表的时候也不会包含新表的数据。

  被复制的列和约束并不使用相同的名称进行融合。如果明确的指定了相同的名称或者在另外一个LIKE子句中，将会报错。

  

  如果建表语句中包括LIKE子句和WITH子句、PARTITION BY子句时，建表将会使用SQL中指定的WITH信息和分区信息，而不再使用源表的信息。对于CHARSET、COMMENT等信息，同时指定时使用源表的信息。

• AUTO_INCREMENT [ = ] value

  这个子句为自动增长列指定一个初始值，value必须为正数，不得超过2¹²⁷-1。

• COMMENT [ = ] 'string'
  • COMMENT [ = ] 'string'子句表示给表添加注释。
  • 在column_constraint中的COMMENT 'string'表示给列添加注释。
  • 在table_constraint中的COMMENT 'string'表示给主键和唯一键对应的索引添加注释。
  

  • 表级注释支持的最大字符串长度为2048字符，列级和索引级注释支持的最大长度为1024字符。
  • table_constraint中的COMMENT仅支持主键和唯一键，其他约束不支持。
• WITH ( { storage_parameter = value } [, ... ] )

  这个子句为表或索引指定一个可选的存储参数。用于表的WITH子句还可以包含OIDS=FALSE表示不分配OID。

  参数的详细描述如下所示。

  • FILLFACTOR

    一个表的填充因子（fillfactor）是一个介于10和100之间的百分数。在Ustore存储引擎下，该值得默认值为92，在Astore存储引擎下默认值为100（完全填充）。如果指定了较小的填充因子，INSERT操作仅按照填充因子指定的百分率填充表页。每个页上的剩余空间将用于在该页上更新行，这就使得UPDATE有机会在同一页上放置同一条记录的新版本，这比把新版本放置在其他页上更有效。对于一个从不更新的表将填充因子设为100是最佳选择，但是对于频繁更新的表，选择较小的填充因子则更加合适。

    取值范围：10~100

  • ORIENTATION

    指定表数据的存储方式，该参数设置成功后就不再支持修改，当前M-Compatibility仅支持行存方式。

    取值范围：

    • ROW，表示表的数据将以行式存储。

      行存储适合于OLTP业务，适用于点查询或者增删操作较多的场景。

    默认值：

    若指定表空间为普通表空间，默认值为ROW。

  • STORAGE_TYPE

    指定存储引擎类型，该参数设置成功后就不再支持修改。

    取值范围：

    • USTORE，表示表支持Inplace-Update存储引擎。特别需要注意，使用UStore表，必须要开启track_counts和track_activities参数，否则会引起空间膨胀。
    • ASTORE，表示表支持Append-Only存储引擎。

    默认值：

    不指定ORIENTATION和STORAGE_TYPE时创建表，默认是USTORE存储引擎（表示表支持Inplace-Update存储引擎）。

  • INIT_TD

    创建UStore表时，指定初始化的TD个数，该参数可以通过alter table进行修改。特别需要注意，该参数会影响数据页面存放的单个元组的最大大小，具体换算方法为MAX_TUPLE_SIZE = BLCKSZ - INIT_TD * TD_SIZE，例如用户将INIT_TD数量从4修改为8，单个元组最大大小会减小4 * INIT_TD大小。

    取值范围：2~128，默认值为4。

  • COMPRESSION

    指定表数据的压缩级别，它决定了表数据的压缩比以及压缩时间。一般来讲，压缩级别越高，压缩比也越大，压缩时间也越长；反之亦然。实际压缩比取决于加载的表数据的分布特征。行存表不支持压缩。

    取值范围：行存表不支持压缩，默认值为NO。

  • COMPRESSLEVEL

    指定表数据同一压缩级别下的不同压缩水平，它决定了同一压缩级别下表数据的压缩比以及压缩时间。对同一压缩级别进行了更加详细的划分，为用户选择压缩比和压缩时间提供了更多的空间。总体来讲，此值越大，表示同一压缩级别下压缩比越大，压缩时间越长；反之亦然。

    取值范围：0~3，默认值为0。

  • segment

    使用段页式的方式存储。本参数仅支持行存表。不支持1-5号物理文件非法删除破坏场景的防护。

    取值范围：on/off

    默认值：off

  • enable_tde

    指定该表为加密表。数据库会自动将加密表中的数据先加密再存储。使用该参数前，请确保已通过GUC参数enable_tde开启透明加密功能，并通过GUC参数tde_key_info设置访问密钥服务的信息，在《特性指南》中“透明数据加密”章节可获取该参数的详细使用方法。本参数仅支持行存表、段页式表、临时表和unlogged表。

    取值范围：on/off。设置enable_tde=on时，key_type、tde_cmk_id、dek_cipher参数由数据库自动生成，用户无法手动指定或更改。

    默认值：off

  • parallel_workers

    表示创建索引时起的bgworker线程数量，例如2就表示将会起2个bgworker线程并发创建索引。

    取值范围：[0,32]，int类型，0表示关闭并行建索引。

    默认值：不设置该参数，表示未开启并行建索引功能。

  • encrypt_algo

    指定加密表的加密算法，需与enable_tde结合使用。

    取值范围：字符串，有效值为：AES_128_CTR，SM4_CTR。

    默认值：为tde_encrypt_config参数中table_algorithm的赋值。

  • dek_cipher

    数据密钥的密文。用户为表设置enable_tde参数后，数据库自动生成数据密钥。

    取值范围：字符串

    默认值：空

  • key_type

    主密钥的类型。用户为表设置enable_tde参数后，数据库自动从GUC参数tde_key_info中获取主密钥的类型。

    取值范围：字符串

    默认值：空

  • cmk_id

    主密钥的ID。用户为表设置enable_tde参数后，数据库自动从GUC参数tde_key_info中获取主密钥的ID。

    取值范围：字符串

    默认值：空

  • hasuids

    参数开启：更新表元组时，为元组分配表级唯一标识id。

    取值范围：on/off。

    默认值：off。

  • collate

    用于记录表的默认字符序，一般只用于内部存储和导入导出，不推荐用户指定或修改。

    取值范围：支持的字符序的oid。

    默认值：0。

  • stat_state

    标识该表的统计信息是否被锁定，如果被锁定了，该表的统计信息无法更新。

    取值范围：locked、unlock。

    默认值：unlock。

• ON COMMIT { PRESERVE ROWS | DELETE ROWS }

  ON COMMIT选项决定在事务中执行创建临时表操作，当事务提交时，此临时表的后续操作。有以下两个选项，当前支持PRESERVE ROWS和DELETE ROWS选项。

  • PRESERVE ROWS（缺省值）：提交时不对临时表做任何操作，临时表及其表数据保持不变。
  • DELETE ROWS：提交时删除临时表中数据。
• TABLESPACE tablespace_name

  创建新表时指定此关键字，表示新表将要在指定表空间内创建。如果没有声明，将使用默认表空间。

  注：需要在非M-Compatibility数据库中创建或删除tablespace。

• CONSTRAINT constraint_name

  列约束或表约束的名称。可选的约束子句用于声明约束，新行或者更新的行必须满足这些约束才能成功插入或更新。

  定义约束有两种方法：

  • 列约束：作为一个列定义的一部分，仅影响该列。
  • 表约束：不和某个列绑在一起，可以作用于多个列。
• NOT NULL

  字段值不允许为NULL。

• NULL

  字段值允许为NULL ，这是缺省值。

  这个子句只是为和非标准SQL数据库兼容。不建议使用。

• CHECK ( expression )

  CHECK约束声明一个布尔表达式，每次要插入的新行或者要更新的行的新值必须使表达式结果为真或未知才能成功，否则会抛出一个异常并且不会修改数据库。

  

  • expression表达式中，如果存在“<>NULL”或“！=NULL”，这种写法是无效的，需要修改为“is NOT NULL”。
  • 不开启精度传递开关（m_format_behavior_compat_options不开启enable_precision_decimal选项）时创建的带有CHECK约束的表，与开启精度传递开关后创建的带有CHECK约束的表在浮点数的比较结果等方面可能出现不一致的情况。如果用户需要在开启精度传递开关的情况下使用未开启精度开关时创建的带有CHECK约束的表，建议在开启精度传递开关后使用ALTER TABLE语法重新定义CHECK约束。

    m_db=# SET m_format_behavior_compat_options='';
    SET
    m_db=# CREATE TABLE mm1(a float(10, 4), b float(5, 3), CHECK(a/b=1.7142858) STORED);
    CREATE TABLE
    m_db=# INSERT INTO mm1 VALUES(1.2, 0.7);
    ERROR:  New row in relation "mm1" violates check constraint "mm1_check".
    DETAIL:  N/A
    m_db=# SET m_format_behavior_compat_options='enable_precision_decimal';
    SET
    m_db=# INSERT INTO mm1 VALUES(1.2, 0.7);
    INSERT 0 1
    m_db=# CREATE TABLE mm2(a float(10, 4), b floaT(5, 3), CHECK(a/b=1.7142858) STORED);
    CREATE TABLE
    m_db=# INSERT INTO mm2 VALUES(1.2, 0.7);
    ERROR:  New row in relation "mm2" violates check constraint "mm2_check".
    DETAIL:  N/A
    
    m_db=# DROP TABLE mm1, mm2;
    CREATE TABLE

• DEFAULT default_expr
  • 使用DEFAULT子句给字段指定缺省表达式，缺省表达式将被用于任何未声明该字段数值的插入操作。如果没有指定缺省值则缺省值为NULL 。
  • 当未在缺省表达式外嵌套括号时，支持指定以下内容：常量、带正负号的数值常量、update_expr。
  • 当在缺省表达式外嵌套括号时，支持指定以下内容：常量、带正负号的数值常量、update_expr、CURRENT_TIME/CURTIME函数、CURRENT_DATE/CURDATE函数（CURRENT_TIME/CURRENT_DATE支持不带括号形式的调用）。
  • 仅支持在TIMESTAMP、DATETIME类型的字段上指定update_expr作为默认值，且字段的精度与update_expr的精度须保持一致。
• ON UPDATE now_expr

  ON UPDATE子句为字段的一种属性约束。

  当对表中某元组执行UPDATE操作时，若更新字段的新值和表中旧值不相同，则表中该元组上具有该属性且不在更新字段内的字段值自动更新为当前时间戳；若更新字段的新值和表中旧值相同，则表中该元组上具有该属性且不在更新字段内的字段值不变，保持原有值；若具有该属性的字段在更新字段内，则对应这些字段值直接按指定更新的值更新。

  

  • 语法上update_expr支持CURRENT_TIMESTAMP 、LOCALTIMESTAMP 、LOCALTIME、NOW()四种关键字，也支持关键字带括号指定或不指定精度。例如：ON UPDATE CURRENT_TIMESTAMP()、ON UPDATE CURRENT_TIMESTAMP(5)、ON UPDATE LOCALTIMESTAMP()、ON UPDATE LOCALTIMESTAMP(6)等。不带括号或空括号时精度为0，其中NOW关键字不支持不带括号。四种关键字互为同义词，属性效果相同。
  • 该属性支持在如下类型的列上指定: TIMESTAMP、DATETIME。
  • 该属性指定的精度和对应列上类型指定的精度必须一致，否则会触发报错。例如：CREATE TABLE t1 (col1 timestamp(6) ON UPDATE CURRENT_TIMESTAMP(6)); 若精度不一致，会产生ERROR: Invalid ON UPDATE clause for "col1" 报错。
  • 该属性和生成列约束不能同时指定同一列。
  • 分区表中的分区键不支持指定该属性。
• [GENERATED ALWAYS] AS ( generation_expr ) [STORED | VIRTUAL]

  该子句将字段创建为生成列，通过指定STORED和VIRTUAL关键字表明生成列的列值存储方式：

  • STORED：创建存储生成列，列值需要存储，在插入或更新元组时，由生成列表达式计算存储生成列的列值。
  • VIRTUAL：创建虚拟生成列，列值不需要存储，在查询语句涉及虚拟生成列时，由生成列表达式计算列值。
  

  • STORED和VIRTUAL关键字可省略，缺省为STORED，在参数m_format_dev_version为s2条件下缺省为VIRTUAL。
  • 生成表达式不能以任何方式引用当前行以外的其他数据。生成表达式支持引用早定义于当前生成列的其他生成列，不能引用系统列。生成列表达式不能引用系统变量、用户自定义变量、存储过程中的变量。生成列表达式不支持引用自增列，且生成列不支持定义自增列属性。生成表达式不能返回结果集，不能使用子查询，不能使用聚集函数、窗口函数和自定义函数。生成表达式调用的函数只能是不可变（IMMUTABLE）函数。
  • 不支持为生成列指定默认值。
  • 存储生成列支持作为分区键的一部分，当作为分区键的一部分时，不支持ALTER TABLE修改存储生成列。
  • 虚拟生成列不支持作为分区键的一部分。
  • 虚拟生成列不支持被外键约束引用。
  • 不支持在虚拟生成列上创建索引。
  • 存储生成列不能和ON UPDATE约束子句的CASCADE,SET NULL,SET DEFAULT动作同时指定。存储生成列不能和ON DELETE约束子句的SET NULL,SET DEFAULT动作同时指定。
  • 在参数m_format_dev_version为s2条件下，存储生成列的基列不能和ON UPDATE或者ON DELETE约束子句的CASCADE, SET NULL, SET DEFAULT动作同时指定。
  • 修改和删除生成列的方法和普通列相同。删除生成列依赖的基列，生成列被自动删除。在参数m_format_dev_version为s2条件下，删除生成列依赖的基列之前需要先删除对应的生成列。
  • 生成列不能被直接写入。在INSERT或UPDATE命令中, 不能为生成列指定值, 但是可以指定关键字DEFAULT。向可被更新的视图中插入生成列数据时，不能为生成列指定值，但是可以指定关键字DEFAULT。
  • 当存储生成列带CHECK约束时，在参数m_format_dev_version为s2的条件下，ALTER TABLE修改存储生成列定义，如果存在生成列不满足CHECK约束，ALTER TABLE语句执行失败，系统报ERROR。
  • 生成列的权限控制和普通列一样。
  • 不开启精度传递开关（m_format_behavior_compat_options不开启enable_precision_decimal选项）时创建的带有生成列的表，与开启精度传递开关后创建的带有生成列的表在浮点数的比较结果等方面可能出现不一致的情况。如果用户需要在开启精度传递开关的情况下使用未开启精度开关时创建的带有生成列的表，建议在开启精度传递开关后使用ALTER TABLE语法重新定义生成列。

    m_db=# SET m_format_behavior_compat_options='';
    SET
    m_db=# CREATE TABLE mm1(a float(10, 4), b float(5, 3), c boolean AS ((a/b)=1.7142858) STORED);
    CREATE TABLE
    m_db=# INSERT INTO mm1 VALUES(1.2, 0.7);
    INSERT 0 1
    m_db=# SELECT * FROM mm1;
       a    |   b   | c 
    --------+-------+---
     1.2000 | 0.700 | 0
    (1 row)
    
    m_db=# SET m_format_behavior_compat_options='enable_precision_decimal';
    SET
    m_db=# CREATE TABLE mm2(a float(10, 4), b float(5, 3), c boolean AS ((a/b)=1.7142858) STORED);
    CREATE TABLE
    m_db=# INSERT INTO mm1 VALUES(1.2, 0.7);
    INSERT 0 1
    m_db=# INSERT INTO mm2 VALUES(1.2, 0.7);
    INSERT 0 1
    m_db=# SELECT * FROM mm1;
       a    |   b   | c 
    --------+-------+---
     1.2000 | 0.700 | 0
     1.2000 | 0.700 | 1
    (2 rows)
    
    m_db=# SELECT * FROM mm2;
       a    |   b   | c 
    --------+-------+---
     1.2000 | 0.700 | 0
    (1 row)
    
    m_db=# DROP TABLE mm1, mm2;
    DROP TABLE

• AUTO_INCREMENT

  该关键字将字段指定为自动增长列。

  自动增长列建议设置索引，并需要作为索引的第一个字段，否则建表时产生警告。

  若在插入时不指定此列的值（或指定此列的值为0、NULL、DEFAULT），此列的值将由自增计数器自动增长得到。

  若插入或更新此列为一个大于当前自增计数器的值，执行成功后，自增计数器将刷新为此值。

  自增初始值由“AUTO_INCREMENT [ = ] value”子句设置，若不设置，默认为1。

  

  • 自动增长列数据类型只能为整数类型、4字节或8字节浮点类型、布尔类型。当自增值已经达到字段数据类型的最大值时，继续自增将产生错误。
  • 每个表只能有一个自动增长列。
  • 自动增长列建议设置索引，并需要作为索引的第一个字段，否则建表时产生警告，含有自动增长列的表进行某些操作时会产生错误，例如：ALTER TABLE EXCHANGE PARTITION。
  • 自动增长列不能指定DEFAULT缺省值。
  • CHECK约束的表达式中不能含有自动增长列，生成列的表达式中不能含有自动增长列。
  • 可以指定自动增长列允许NULL，若不指定，默认自动增长列含有NOT NULL约束。
  • 含有自动增长列的表创建时，会创建一个依赖于此列的序列作为自增计数器，不允许通过序列相关功能修改或删除此序列，可以查看序列的值。请勿使其他序列依赖或关联此自动增长列。
  • 本地临时表中的自动增长列不会创建序列。
  • 自增计数器自增和刷新操作不会回滚。
    • 数据插入到表之前，0/NULL会触发自增。数据插入或更新到表之后，会更新自增计数器。如果在自增之后出现了报错，数据没有插入或更新到表中，此时自增计数器不会回滚。后续插入语句基于自增计数器触发自增，会出现表中自动增长列的值不连续的情况。
    • 批量插入或导入预留自增缓存值也有可能产生自动增长列的值不连续的情况，详见auto_increment_cache参数说明。
• [DEFAULT] {CHARACTER SET | CHAR SET | CHARSET} [ = ] default_charset

  指定表的默认字符集。单独指定时会将表的默认字符序设置为指定的字符集的默认字符序。

• [DEFAULT] COLLATE [ = ] default_collation

  指定表的默认字符序。单独指定时会将表的默认字符集设置为指定的字符序对应的字符集。

  字符序请参见字符集与字符序。

  

  表的字符集或字符序未显式指定时，若指定了模式的默认字符集或字符序，表字符集和字符序将从模式上继承。

• 列级唯一约束：UNIQUE [KEY] index_parameters

  UNIQUE约束表示表里的一个或多个字段的组合必须在全表范围内唯一。

  UNIQUE KEY与UNIQUE语义相同。

• 表级唯一约束：UNIQUE [INDEX | KEY][ index_name ][ USING method ]( {{ column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] }[, ... ] ) index_parameters

  UNIQUE约束表示表里的一个字段或多个字段的组合必须在全表范围内唯一。

  对于唯一约束，NULL被认为是互不相等的。

  column_name(length)是前缀键，详见：•column_name ( length )。

  index_name为索引名。

  

  对于唯一键约束，constraint_name和index_name同时指定时，索引名为index_name。

• 列级主键约束：[PRIMARY] KEY index_parameters

  表级主键约束：PRIMARY KEY [index_name] [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ） index_parameters

  主键约束声明表中的一个或者多个字段只能包含唯一的非NULL值。

  一个表只能声明一个主键。

• 表列字段约束：REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ]

  表级别约束：FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ]

  

  表列字段约束REFERENCES在数据库M-compatibility模式兼容版本控制开关s1及以上版本时，语法不报错也没有提示，但实际不生效（如m_format_dev_version = 's1'）。

  外键约束要求新表中一列或多列构成的组应该只包含、匹配被参考表中被参考字段值。若省略refcolumn，则将使用reftable的主键。被参考列应该是被参考表中的唯一字段或主键。外键约束不能被定义在临时表和永久表之间。

  参考字段与被参考字段之间存在三种类型匹配，分别是：

  • MATCH FULL：不允许一个多字段外键的字段为NULL，除非全部外键字段都是NULL。
  • MATCH SIMPLE（缺省）：允许任意外键字段为NULL。

  另外，当被参考表中的数据发生改变时，某些操作也会在新表对应字段的数据上执行。ON DELETE子句声明当被参考表中的被参考行被删除时要执行的操作。ON UPDATE子句声明当被参考表中的被参考字段数据更新时要执行的操作。对于ON DELETE子句、ON UPDATE子句的可能动作：

  • NO ACTION（缺省）：删除或更新时，创建一个表明违反外键约束的错误。
  • RESTRICT：删除或更新时，创建一个表明违反外键约束的错误。
  • CASCADE：删除新表中任何引用了被删除行的行，或更新新表中引用行的字段值为被参考字段的新值。
  • SET NULL：设置引用字段为NULL。
  • SET DEFAULT：设置引用字段为它们的缺省值。
  

  • 外键约束的完整性检查由GUC参数foreign_key_checks进行控制，foreign_key_checks可以设置为on/off，分别表示启用/关闭外键约束的完整性检查，默认情况下为on。
• USING INDEX TABLESPACE tablespace_name

  为UNIQUE或PRIMARY KEY约束相关的索引声明一个表空间。如果没有提供这个子句，这个索引将在default_tablespace中创建，如果default_tablespace为空，将使用数据库的缺省表空间。

  

  该属性在数据库M-compatibility模式兼容版本控制开关为s1时不支持（即m_format_dev_version = 's1'）。

建表示例

• 临时表

  --创建表临时表。
  m_db=# CREATE TEMPORARY TABLE test_t1(
      id       CHAR(7),
      name     VARCHAR(20),
      province VARCHAR(60),                       --省
      country   VARCHAR(30) DEFAULT 'China'        --国籍
  );
  
  -- 在当前会话中插入数据。
  m_db=# INSERT INTO test_t1 VALUES ('0000009','Jack','Guangzhou','China');
  
  --临时表里面的数据只在当前事务中有效，所以在另一个会话中查看该表中没有数据。
  m_db=# SELECT * FROM test_t1;
   id | name | age 
  ----+------+-----
  (0 rows)

  --事务中创建表临时表，并指定提交事务时删除该临时表数据。
  m_db=# START TRANSACTION;
  m_db=# CREATE TEMPORARY TABLE test_t2(
      id       CHAR(7),
      name     VARCHAR(20),
      province VARCHAR(60),                       --省
      country   VARCHAR(30) DEFAULT 'China'        --国籍
  ) ON COMMIT DELETE ROWS;
  m_db=# INSERT test_t2 VALUES('aid','aname','aprovince','China');
  m_db=# COMMIT;
  m_db=# SELECT * FROM test_t2;
   id | name | province | country
  ----+------+----------+---------
  (0 rows)
  --删除表。
  m_db=# DROP TABLE test_t1;
  m_db=# DROP TABLE test_t2;

• IF NOT EXIST关键字

  使用该关键字，表不存在时报NOTICE；如不用该关键字，则报ERROR。两种情况下表都不会创建成功。

  m_db=# CREATE TABLE test_t3(id INT);
  --创建一个已经存在同名的表test_t3。
  m_db=# CREATE TABLE test_t3(id INT);
  ERROR:  relation "test_t3" already exists in schema "public"
  DETAIL:  creating new table with existing name in the same schema
  
  --使用IF NOT EXISTS关键字。
  m_db=# CREATE TABLE IF NOT EXISTS test_t3(id INT);
  NOTICE:  relation "test_t3" already exists, skipping
  CREATE TABLE
  
  --删除表。
  m_db=# DROP TABLE test_t3;

建表添加约束示例

• 非空约束
  非空约束的字段，如果在添加数据时没有指定值，就会报错。可以为表中多个字段添加非空约束。

  --建表并给id字段添加非空约束。
  m_db=# CREATE TABLE test_t4(
      id       CHAR(7) NOT NULL,
      name     VARCHAR(20),
      province VARCHAR(60),                       --省
      country   VARCHAR(30) DEFAULT 'China'        --国籍
  );
  
  --插入数据时,如果不指定id的值或者id为NULL，会触发非空约束,导致插入失败。
  m_db=# INSERT INTO test_t4 (name,province) VALUES ('scott','Shanghai');
  ERROR:  null value in column "id" violates not-null constraint
  DETAIL:  Failing row contains (null, scott, Shanghai, China) 
  
  --删除表。
  m_db=# DROP TABLE test_t4;

• 唯一约束
  关键字UNIQUE给字段添加一个唯一约束，插入数据时该字段如有重复则触发约束，多个NULL不算重复，添加唯一约束时，会自动增加一个唯一索引。可以为表中多个字段添加唯一约束。

  --建表添加唯一约束。
  m_db=# CREATE TABLE test_t5(
      id       CHAR(7) UNIQUE,
      name     VARCHAR(20),
      province VARCHAR(60),                       --省
      country   VARCHAR(30) DEFAULT 'China'        --国籍
  );
  
  --也可以用如下写法，人工为唯一约束命名，以及为多个字段添加约束。
  m_db=# CREATE TABLE test_t6(
      id       CHAR(7),
      name     VARCHAR(20),
      province VARCHAR(60),                        --省
      country   VARCHAR(30) DEFAULT 'China',        --国籍
      CONSTRAINT unq_test_id UNIQUE (id,name)
  );

  --插入id重复的数据，触发约束，导致插入失败。
  m_db=# INSERT INTO test_t5(id) VALUES('0000010');
  INSERT 0 1
  m_db=# INSERT INTO test_t5(id) VALUES('0000010');
  ERROR:  duplicate key value violates unique constraint "test_t5_id_key"
  DETAIL:  Key (id)=(0000010) already exists.

  --多次插入id是NULL的数据不会触发约束。
  m_db=# INSERT INTO test_t5(id) VALUES (NULL);
  INSERT 0 1
  m_db=# INSERT INTO test_t5(id) VALUES (NULL);
  INSERT 0 1
  
  m_db=# SELECT * FROM test_t5;
     id    | name | province | country 
  ---------+------+----------+--------
   0000010 |      |          | China
           |      |          | China
           |      |          | China
  
  --删除表。
  m_db=# DROP TABLE test_t5;
  m_db=# DROP TABLE test_t6;

• 主键约束

  关键字PRIMARY KEY给字段添加主键约束，要求字段唯一且不为空。添加主键约束时自动为该表创建唯一索引，也会为该字段自动增加一个非空约束。

  每个表里面只能定义一个主键约束，不能定义多个。

  --建表添加主键约束。
  m_db=# CREATE TABLE test_t6(
      id       CHAR(7) PRIMARY KEY,
      name     VARCHAR(20),
      province VARCHAR(60),                       --省
      country   VARCHAR(30) DEFAULT 'China'        --国籍
  );
  m_db=# INSERT INTO test_t6 (id,name,province) VALUES ('0000001','july','Beijing');
  
  --也可以用如下写法，人工为唯一约束命名，以及为多个字段添加约束。
  m_db=# CREATE TABLE test_t7(
      id       CHAR(7),
      name     VARCHAR(20),
      province VARCHAR(60),                       --省
      country   VARCHAR(30) DEFAULT 'China',        --国籍
      CONSTRAINT pk_test_t6_id PRIMARY KEY (id,name)
  );

  --插入id为NULL的数据，触发约束。
  m_db=# INSERT INTO test_t6 (id,name,province) VALUES (NULL,'july','Beijing'); 
  ERROR:  null value in column "id" violates not-null constraint
  DETAIL:  Failing row contains (null, july, Beijing, China).
  
  --插入id重复的数据，触发约束。
  m_db=# INSERT INTO test_t6 (id,name,province) VALUES ('0000001','ben','Shanghai');
  ERROR:  duplicate key value violates unique constraint "test_t6_pkey"
  DETAIL:  Key (id)=(0000001) already exists.

  --删除表。
  m_db=# DROP TABLE test_t6;
  m_db=# DROP TABLE test_t7;

• 检查约束

  关键字CHECK给字段添加检查约束，在检查约束中必须引用表中的一个或多个字段，并且表达式返回结果必须是一个布尔值。在表达式中不能包含子查询。对同一个字段可以同时定义检查约束和非空约束。

  --建表，添加检查约束。
  m_db=# CREATE TABLE test_t8 (
      id       CHAR(7),
      name     VARCHAR(20),
      age      INT CHECK(age > 0 AND age < 150)
  );
  
  --也可以使用如下SQL，人工为检查约束命名以及为一个或者多个字段添加检查约束。
  m_db=# CREATE TABLE test_t9 (
      id       CHAR(7),
      name     VARCHAR(20),
      age      INT,
      CONSTRAINT chek_test_t8_age CHECK(age > 0 AND age < 150)
  );

  --插入不符合表达式的值，会触发检查约束导致插入失败。
  m_db=# INSERT INTO test_t8 (id,name,age) VALUES ('0000007','scott',200);
  ERROR:  new row for relation "test_t8" violates check constraint "test_t8_age_check"
  DETAIL:  N/A

  --删除表。
  m_db=# DROP TABLE test_t8;
  m_db=# DROP TABLE test_t9;

• 外键约束

  当两个表包含一个或多个公共列时，可以通过外键约束来强制关联两个表。

  • FOREIGN KEY：用来指定该表中和被引用的表有关系的字段。
  • REFERENCES：用来指定被引用的表和原表有关系的字段。

  外键约束的特点:

  • 定义为外键约束的字段中只能包含相应的其他表中引用字段的值或NULL。
  • 可以为一个字段或者多个字段定义外键约束。
  • 定义了外键约束的字段和相应的引用字段可以存在同一个表中，称为自引用。
  • 对同一个字段可以同时定义外键和非空约束。
  • 主表中被应用的列，必须有主键约束或唯一约束。

  --创建部门表。
  m_db=# CREATE TABLE dept(
      deptno INT PRIMARY KEY,
      loc VARCHAR(200)
  );
  
  --创建员工表，添加外键约束。
  m_db=# CREATE TABLE emp(
      empno INT,
      name VARCHAR(50),
      deptno INT,
      CONSTRAINT fk_emp FOREIGN KEY (deptno) REFERENCES dept(deptno)
  );

  --部门表插入数据。
  m_db=# INSERT INTO dept VALUES (10,'Beijing');
  m_db=# INSERT INTO dept VALUES (20,'Beijing');
  m_db=# INSERT INTO dept VALUES (30,'Shanghai');
  
  --员工表中插入deptno在部门表中能找到的数据。
  m_db=# INSERT INTO emp VALUES (1,'Bob',10);
  
  --员工表中插入deptno为NULL的数据。
  m_db=# INSERT INTO emp VALUES (2,'Scott',NULL);
  
  --员工表中插入deptno在部门表中找不到的数据。
  m_db=# INSERT INTO emp VALUES (1,'Jack',999);
  ERROR:  insert or update on table "emp" violates foreign key constraint "fk_emp"
  DETAIL:  Key (deptno)=(999) is not present in table "dept".

  --查看数据。
  m_db=# SELECT * FROM emp;
   empno | name  | deptno 
  -------+-------+--------
       1 | Bob   |     10
       2 | Scott |       
  (2 rows)

  --删除表。
  m_db=# DROP TABLE emp;
  m_db=# DROP TABLE dept;

相关链接

ALTER TABLE，DROP TABLE

优化建议

• UNLOGGED
  • UNLOGGED表和表上的索引因为数据写入时不通过WAL日志机制，写入速度远高于普通表。因此，可以用于缓冲存储复杂查询的中间结果集，增强复杂查询的性能。
  • UNLOGGED表无主备机制，在系统故障或异常断点等情况下，会有数据丢失风险，因此，不可用来存储基础数据。
• TEMPORARY
  • 非全局临时表只在当前会话可见，会话结束后会自动删除。
• LIKE
  • 新表自动从这个表中继承所有字段名及其数据类型和非空约束，新表与源表之间在创建动作完毕之后是完全无关的。
• ORIENTATION ROW
  • 创建行存表，行存储适合于OLTP业务，此类型的表上交互事务比较多，一次交互会涉及表中的多个列，用行存查询效率较高。