文档首页/ 云数据库 GaussDB/ M-Compatibility开发指南（集中式）/ SQL参考/ SQL语法/ SQL语句/ A/ ALTER TABLE
更新时间：2025-09-12 GMT+08:00
查看PDF
分享

ALTER TABLE

功能描述

修改表，包括修改表的定义、重命名表、重命名表中指定的列、设置表的所属模式、添加/更新多个列。

注意事项

  • 表的所有者、被授予了表ALTER权限的用户或被授予ALTER ANY TABLE权限的用户有权限执行ALTER TABLE命令，系统管理员默认拥有此权限。但要修改表的所有者或者修改表的模式，当前用户必须是该表的所有者或者系统管理员，且该用户是新所有者角色的成员。
  • 不能修改分区表的TABLESPACE，但可以修改分区的TABLESPACE。
  • 不支持修改存储参数ORIENTATION。
  • SET SCHEMA操作不支持修改为系统内部模式，当前仅支持用户模式之间的修改。
  • 不支持增加DEFAULT值中包含nextval()表达式的列。
  • 通过约束名删除PRIMARY KEY约束时，不会删除NOT NULL约束，如果有需要，请手动删除NOT NULL约束。
  • 使用JDBC时，支持通过PrepareStatement对DEFAULT值进行参数化设置。
  • 如果用ADD COLUMN增加一个字段，那么所有表中现有行都初始化为该字段的缺省值（如果没有声明DEFAULT子句，默认值为NULL）。
    • 新增列没有声明DEFAULT值时，默认值为NULL，不会触发全表更新。
    • 新增列如果有DEFAULT值，必须符合以下所有要求，否则会带来全表更新的操作，影响在线业务：
      • 数据类型必须为以下类型：TINYINT、SMALLINT、BIGINT、INTEGER、NUMERIC、DECIMAL、BOOL、FLOAT、DOUBLE、CHAR、VARCHAR、TEXT、TIMESTAMP、DATE、TIME。
      • 新增列的DEFAULT值长度不超过128个字节。
      • 新增列DEFAULT值不包含易变（volatile）函数。
      • 新增列设置有DEFAULT值，且DEFAULT值不为NULL。
      • 如果不确定是否满足新增列DEFAULT值不包含易变（volatile）函数的条件，可以查询pg_rpoc系统表中函数的provolatile属性是否为'v'进行判断。
  • 使用FIRST | AFTER column_name新增列或修改列，或修改字段的字符集，会带来全表更新的操作，影响在线业务。

语法格式

  • 修改表的定义。 
    ALTER TABLE { table_name [*] | ONLY table_name | ONLY ( table_name ) }
    action [, ... ];
    其中具体表操作action可以是以下子句之一： 
    column_clause
    | ADD [CONSTRAINT] table_constraint
    | DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    | SET ( {storage_parameter = value} [, ... ] )     
    | RESET ( storage_parameter [, ... ] )
    | OWNER TO new_owner
    | TO { GROUP groupname | NODE ( nodename [, ... ] ) }
    | ADD NODE ( nodename [, ... ] )
    | DELETE NODE ( nodename [, ... ] )
    | AUTO_INCREMENT [ = ] value
    | COMMENT [ = ] 'string'
    | [ [ DEFAULT ] {CHARACTER SET | CHAR SET | CHARSET} [ = ] charset_name] [ [ DEFAULT ] COLLATE [ = ] collation_name]
    | CONVERT TO {CHARACTER SET | CHAR SET | CHARSET} charset_name [COLLATE collation_name ]
    | ADD index_clause
    | DROP {INDEX | KEY} index_name
    | DROP PRIMARY KEY
    | DROP FOREIGN KEY fk_name
    | REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
    • ADD [CONSTRAINT] table_constraint

      给表增加一个新的约束。

    • DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]

      删除一个表上的约束。

    • SET ( {storage_parameter = value} [, ... ] )

      修改表的一个或多个存储参数。

    • RESET ( storage_parameter [, ... ] )

      重置表的一个或多个存储参数。

    • OWNER TO new_owner

      将表、序列、视图的所有者改变成指定的用户，用户名要求详见•user_name。

    • TO { GROUP groupname | NODE ( nodename [, ... ] ) }

      此语法仅在扩展模式（GUC参数support_extended_features为on时）下可用。该模式谨慎打开，主要供内部扩容工具使用，一般用户不应使用该模式。

    • ADD NODE ( nodename [, ... ] )

      此语法主要供内部扩容工具使用，一般用户不建议使用。

    • DELETE NODE ( nodename [, ... ] )

      此语法主要供内部缩容工具使用，一般用户不建议使用。

    • AUTO_INCREMENT [ = ] value

      设置自动增长列下一次的自增值。设置的值只有大于当前自增计数器时才会生效。

      value必须是非负数，且不得大于2127-1。

    • [ [ DEFAULT ] {CHARACTER SET | CHAR SET | CHARSET} [ = ] default_charset ] [ [ DEFAULT ] COLLATE [ = ] default_collation ]

      将表的默认字符集和默认字符序修改为指定的值。修改不会影响表中当前已经存在的列。

    • CONVERT TO {CHARACTER SET | CHAR SET | CHARSET} charset [ COLLATE collation ]

      将表的默认字符集和默认字符序修改为指定的值，同时将表中的所有字符类型的字段的字符集和字符序设置为指定的值，并将字段里的数据转换为新字符集编码。

      该语法不支持分区表修改字符序。

    • DROP {INDEX | KEY} index_name

      删除表上的索引。INDEX和KEY为同义词。

    • DROP PRIMARY KEY

      删除表上的主键约束。

    • DROP FOREIGN KEY fk_name

      删除指定的外键约束。

    • REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
      在逻辑复制场景下，指定该表的UPDATE和DELETE操作中旧元组的记录级别。
      • DEFAULT记录主键的列的旧值，没有主键则不记录。
      • USING INDEX记录命名索引覆盖的列的旧值，这些值必须是唯一的、不局部的、不可延迟的，并且仅包括标记为NOT NULL的列。
      • FULL记录该行中所有列的旧值。
      • NOTHING不记录有关旧行的信息。

      在逻辑复制场景，解析该表的UPDATE和DELETE操作语句时，解析出的旧元组由以此方法记录的信息组成。对于有主键表该选项可设置为DEFAULT或FULL。对于无主键表该选项需设置为FULL，否则解码时旧元组将解析为空。一般场景不建议设置为NOTHING，旧元组会始终解析为空。

      针对ustore表，选项NOTHING无效，实际效果等同于FULL；DEFAULT没有主键时，记录该行所有列。

    • 其中列相关的操作column_clause可以是以下子句之一： 
      ADD [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET}  charset ][ COLLATE collation ] [ column_constraint [ ... ] ] [ FIRST | AFTER column_name ]   
| ADD ({ column_name data_type [ compress_mode ]} [, ...] )
| MODIFY column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ]
| MODIFY column_name [ CONSTRAINT constraint_name ] NULL
| MODIFY [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET}  charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [ FIRST | AFTER column_name ]
| CHANGE [ COLUMN ] old_column_name new_column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET}  charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [ FIRST | AFTER column_name ]
| DROP [ COLUMN ]  column_name [ RESTRICT | CASCADE ]    
| ALTER [ COLUMN ] column_name { SET DEFAULT default_expr | DROP DEFAULT }  
| MODIFY column_name data_type [GENERATED ALWAYS] AS generation_expr [STORED]
      • ADD [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [ COLLATE collation ] [ column_constraint [ ... ] ] [ FIRST | AFTER column_name]

        向表中增加一个新的字段。用ADD COLUMN增加一个字段，所有表中现有行都初始化为该字段的缺省值（如果没有声明DEFAULT子句，值为NULL）。其中FIRST | AFTER column_name表示新增字段到某个位置。

      • ADD ( { column_name data_type [ compress_mode ] } [, ...] )

        向表中增加多列。

      • MODIFY ( { column_name data_type | column_name [ CONSTRAINT constraint_name ] NOT NULL [ ENABLE ] | column_name [ CONSTRAINT constraint_name ] NULL } [, ...] )

        修改表已存在字段的数据类型。此命令会导致该字段的统计信息清空，建议在修改后重新收集该列的统计信息。

      • MODIFY [ COLUMN ] column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [FIRST | AFTER column_name]
        • 修改表已存在字段的定义，将用新定义替换字段原定义，原字段上的索引、独立对象约束（例如：主键、唯一键、CHECK约束等）不会被删除。[FIRST | AFTER column_name]语法表示修改字段定义的同时修改字段在表中的位置。
        • 不支持修改分区键信息，即不支持指定column_name为分区键。
        • 被修改数据类型或排序规则的字段如果被一个生成列引用，这个生成列的数据将会重新生成。
        • 被修改字段若被一些对象依赖（比如：索引、独立对象约束、视图等），修改字段过程中将会重建这些对象。若被修改后字段定义违反此类对象的约束，修改操作会失败，比如：修改作为视图结果列的字段的数据类型。请修改字段前评估这类影响。
        • 被修改字段若被一些对象调用，修改字段不会处理这些对象。修改字段完毕后，这些对象有可能出现不可用的情况，请修改字段前评估这类影响。
        • 修改字段的字符集或字符序会将字段中的数据转换为新的字符集进行编码。
        • 此命令会导致该字段的统计信息清空，建议在修改后重新收集该列的统计信息。
      • CHANGE [ COLUMN ] old_column_name new_column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [{[ COLLATE collation ] | [ column_constraint ]} [ ... ] ] [FIRST | AFTER column_name]
        • 修改表已存在字段的名称和定义，字段新名称不能是已有字段的名称，将用新名称和定义替换字段原名称和定义。原字段上的索引、独立对象约束（例如：主键、唯一键、CHECK约束）等不会被删除。[FIRST | AFTER column_name]语法表示修改字段名称和定义的同时修改字段在表中的位置。
        • 不支持修改分区键字段的数据类型和排序规则，不支持修改规则引用的字段的数据类型和排序规则。
        • 被修改数据类型或排序规则的字段如果被一个生成列引用，这个生成列的数据将会重新生成。
        • 被修改字段若被一些对象依赖（比如：索引、独立对象约束、视图等），修改字段过程中将会重建这些对象。若被修改后字段定义违反此类对象的约束，修改操作会失败，比如：修改作为视图结果列的字段的数据类型。请修改字段前评估这类影响。
        • 被修改字段若被一些对象调用，修改字段不会处理这些对象。修改字段名称后，这些对象有可能出现不可用的情况，请修改字段前评估这类影响。
        • 修改字段的字符集或字符序会将字段中的数据转换为新的字符集进行编码。
      • DROP [ COLUMN ] column_name [ RESTRICT | CASCADE ]
        • 从表中删除一个字段，和这个字段相关的索引和表约束也会被自动删除。CASCADE选项在数据库M-compatibility模式兼容版本控制开关s1及以上版本（如m_format_dev_version = 's1'）时仅语法支持，但实际不生效。
        • DROP COLUMN命令并不是物理上把字段删除，而只是简单地把它标记为对SQL操作不可见。随后对该表的插入和更新将在该字段存储一个NULL。因此，删除一个字段是很快的，但是它不会立即释放表在磁盘上的空间，因为被删除了的字段占据的空间还没有回收。这些空间将在执行VACUUM时而得到回收。
      • ALTER [ COLUMN ] column_name { SET DEFAULT default_expr | DROP DEFAULT }

        为一个字段设置或者删除缺省值。请注意缺省值只应用于随后的INSERT命令，它们不会修改表中已经存在的行。也可以为视图创建缺省，这个时候它们是在视图的ON INSERT规则应用之前插入到INSERT句中的。

        此处的default_expr支持范围请参考•DEFAULT default_expr

      • MODIFY column_name data_type [GENERATED ALWAYS] AS generation_expr [STORED]

        修改生成列的数据类型和表达式。详见[GENERATED ALWAYS] AS ( generation_expr ) [STORED]。

      • 其中列约束column_constraint为： 
        AUTO_INCREMENT
| COMMENT 'string'
|[ CONSTRAINT constraint_name ]
    { NOT NULL |
      NULL |
      CHECK ( expression ) |
      DEFAULT default_expr  |
      ON UPDATE update_expr |
      [GENERATED ALWAYS] AS ( generation_expr ) [STORED] |
      UNIQUE [KEY] index_parameters |
      [PRIMARY] KEY index_parameters |
      REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL  | MATCH SIMPLE ]
          [ ON DELETE action ] [ ON UPDATE action ] }
      • 其中列的压缩可选项compress_mode为： 
        [ DICTIONARY ]
      • 其中update_expr为： 
        { CURRENT_TIMESTAMP | LOCALTIMESTAMP | NOW() }
    • index_clause 
      {INDEX | KEY} [ [schema_name.] index_name ] [ USING method ] ({column_name [(length)] | (expr) [ASC | DESC]}[,...]) [[COMMENT 'string' | USING method][...]]

      参数说明，详见CREATE INDEX语法。

    • 其中表约束table_constraint为： 
      [ CONSTRAINT [ constraint_name ] ]
    { CHECK ( expression ) |
      UNIQUE [INDEX|KEY][index_name] [USING access_method] ( { { column_name [ ( length ) ] | ( expression ) } [ ASC | DESC ] } [, ... ] ) index_parameters [USING access_method] [comment 'string']|
      PRIMARY KEY [ USING access_method ] [index_name] ( { column_name [ ASC | DESC ] }[, ... ] ) index_parameters [USING access_method] [comment 'string']
      }
      FOREIGN KEY [ idx_name ] ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
         [ MATCH FULL  | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }

      access_method可取值为：

      BTREE
      其中索引参数index_parameters为： 
      [ WITH ( {storage_parameter = value} [, ... ] ) ]
  • 重命名表。对名称的修改不会影响所存储的数据。 
    ALTER TABLE table_name 
    RENAME [TO | AS | =] new_table_name;
  • 重命名表上的索引。 
    ALTER TABLE table_name 
    RENAME {INDEX | KEY} old_index_name TO new_index_name
  • 设置表的所属模式。 
    ALTER TABLE [ IF EXISTS ] table_name 
    SET SCHEMA new_schema;
    • 这种形式把表移动到另外一个模式。相关的索引、约束都跟着移动。目前序列不支持改变schema。 若该表拥有序列，需要将序列删除，重建，或者取消拥有关系， 才能将表schema更改成功。
    • 要修改一个表的模式，用户必须在新模式上拥有CREATE权限。要把该表添加为一个父表的新子表，用户必须同时又是父表的所有者。要修改所有者，用户还必须是新的所有角色的直接或间接成员，并且该成员必须在此表的模式上有CREATE权限。这些限制规定了该用户不能做出了重建和删除表之外的事情。不过，系统管理员可以以任何方式修改任意表的所有权限。
    • 除了RENAME和SET SCHEMA之外所有动作都可以捆绑在一个经过多次修改的列表中并行使用。比如，可以在一个命令里增加几个字段或修改几个字段的类型。对于大表，此种操作带来的效率提升更明显，原因在于只需要对该大表做一次处理。
    • 增加一个CHECK或NOT NULL约束将会扫描该表，以保证现有的行符合约束要求。
    • 用一个非空缺省值增加一个字段或者改变一个字段的现有类型会重写整个表。对于大表来说，这个操作可能会花很长时间，并且它还临时需要两倍的磁盘空间。
  • 添加多个列。 
    ALTER TABLE table_name
    ADD ( { column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]} [, ...] );

参数说明

  • IF EXISTS

    如果不存在相同名称的表，不会抛出一个错误，而会发出一个通知，告知表不存在。

  • table_name [*] | ONLY table_name | ONLY ( table_name )

    table_name是需要修改的表名。

    若声明了ONLY选项，则只有该表被更改。若未声明ONLY，该表及其所有子表都将会被更改。另外，可以在表名称后面显示地增加*选项来指定包括子表，即表示所有后代表都被扫描，这是默认行为。目前ONLY和增加*选项保留语法，但功能不支持。

  • constraint_name
    • 在DROP CONSTRAINT操作中表示要删除的现有约束的名称。
    • 在ADD CONSTRAINT操作中表示新增的约束名称。
  • index_name

    索引名称。

    在ADD CONSTRAINT操作中：

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

    指定创建索引的方法。

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

    在ADD CONSTRAINT操作中：

    • 未指定USING method时，对于ASTORE的存储方式，默认索引方法为btree；对于USTORE的存储方式，默认索引方法为ubtree。
    • 当表的存储方式为USTORE时，SQL语句中约束指定为using btree，底层会自动将约束建立为using ubtree。
  • ASC | DESC

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

  • expression

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

  • storage_parameter

    存储参数的名称。

    支持使用ALTER TABLE SET/RESET语法修改的存储参数如下：

    • parallel_workers

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

      取值类型：int类型

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

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

  • new_owner

    表新拥有者的名称，用户名要求详见•user_name。

  • column_name，column_1_name， column_2_name

    现存的或新字段的名称。

  • data_type

    新字段的类型，或者现存字段的新类型。

  • compress_mode

    表字段的压缩可选项。该子句指定该字段优先使用的压缩算法。行存表不支持压缩。当前M-Compatibility下无法使用。

  • charset

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

  • collation

    字段排序规则（字符序）名称。可选字段COLLATE指定了新字段的排序规则，如果省略，排序规则为新字段的默认类型。排序规则可以使用“select * from pg_collation;”命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。

    还支持utf8mb4_bin、utf8mb4_general_ci、utf8mb4_unicode_ci、binary、gbk_chinese_ci、gbk_bin、gb18030_chinese_ci、gb18030_bin字符序，请参见表级字符集和字符序。

    • 仅字符类型支持指定字符集，指定为BINARY字符集或字符序实际是将字符类型转化为对应的二进制类型，若类型映射不存在则报错。当前仅有TEXT类型转化为BLOB的映射。
    • 除BINARY字符集、字符序外，当前仅支持指定与数据库编码相同的字符集。
    • 字段字符集或字符序未显式指定时，若指定了表的默认字符集或字符序，字段字符集和字符序将从表上继承。
    • 当修改的字符集或字符序对应的字符集与当前字段字符集不同时，会将字段中的数据转换为指定的字符集进行编码。
  • NOT NULL | NULL

    设置列是否允许空值。

  • ENABLE

    表示启动该约束，缺省时默认启用。

  • CHECK ( expression )

    每次将要插入的新行或者将要被更新的行必须使表达式结果为真才能成功，否则会抛出一个异常并且不会修改数据库。

    目前，CHECK表达式不能包含子查询。

  • 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 update_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]

    该子句将字段创建为生成列，生成列的值在写入（插入或更新）数据时由generation_expr计算得到，STORED表示像普通列一样存储生成列的值。

    • STORED关键字可省略，与不省略STORED语义相同。
    • 生成表达式不能以任何方式引用当前行以外的其他数据。生成表达式不能引用其他生成列，不能引用系统列。生成表达式不能返回结果集，不能使用子查询，不能使用聚集函数。生成表达式调用的函数只能是不可变（IMMUTABLE）函数。
    • 不能为生成列指定默认值。
    • 生成列不能作为分区键的一部分。
    • 生成列不能和ON UPDATE约束子句的CASCADE,SET NULL,SET DEFAULT动作同时指定。生成列不能和ON DELETE约束子句的SET NULL,SET DEFAULT动作同时指定。
    • 修改和删除生成列的方法和普通列相同。删除生成列依赖的普通列，生成列被自动删除。不能改变生成列所依赖的列的类型。
    • 生成列不能被直接写入。在INSERT或UPDATE命令中, 不能为生成列指定值, 但是可以指定关键字DEFAULT。
    • 生成列的权限控制和普通列一样。
  • AUTO_INCREMENT

    指定列为自动增长列。

    详见：•AUTO_INCREMENT。

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

    具体请参见：•COMMENT [ = ] 'string'

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

    UNIQUE KEY与UNIQUE语义相同。

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

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

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

    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使用时语法不报错也没有提示，但实际不生效。

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

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

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

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

    • NO ACTION（缺省）：删除或更新时，创建一个表明违反外键约束的错误。
    • RESTRICT：删除或更新时，创建一个表明违反外键约束的错误。与NO ACTION相同。
    • CASCADE：删除新表中任何引用了被删除行的行，或更新新表中引用行的字段值为被参考字段的新值。
    • SET NULL：设置引用字段为NULL。
    • SET DEFAULT：设置引用字段为它们的缺省值。
    • 外键约束的完整性检查由GUC参数foreign_key_checks进行控制，foreign_key_checks可以设置为on/off，分别表示启用/关闭外键约束的完整性检查，默认情况下为on。
  • WITH ( {storage_parameter = value} [, ... ] )

    索引指定一个可选的存储参数。

  • new_table_name

    修改后新的表名称。

  • new_column_name

    表中指定列修改后新的列名称。

  • new_index_name

    修改后表上索引的新名称。

  • new_schema

    修改后新的模式名称。

  • CASCADE | RESTRICT

    CASCADE： 级联删除依赖于被依赖字段或者约束的对象（比如引用该字段的视图）。

    RESTRICT：如果字段或者约束还有任何依赖的对象，则拒绝删除该字段。这是缺省行为。

    该属性在数据库M-compatibility模式兼容版本控制开关s1及以上版本（如m_format_dev_version = 's1'）时仅语法支持，但实际不生效。

  • FIRST

    新增列或修改列到第一位。

  • AFTER column_name

    新增列或修改列到column_name之后。

    有规则依赖的表不支持改变表列的位置（包括新增和修改导致列位置的变化）。

  • schema_name

    表所在的模式名称。

修改表示例

  • 重命名表 
    m_db=# CREATE TABLE aa(c1 int, c2 int);
m_db=# ALTER TABLE aa RENAME TO test_alt1;
  • 修改表所属模式 
    --创建模式test_schema。
m_db=# CREATE SCHEMA test_schema;
--把表test_alt1的所属模式修改为test_schema。
m_db=# ALTER TABLE test_alt1 SET SCHEMA test_schema;
--查询表信息。
m_db=# SELECT schemaname,tablename FROM pg_tables WHERE tablename = 'test_alt1';
 schemaname  | tablename 
-------------+-----------
 test_schema | test_alt1
(1 row)
  • 修改表的所有者 
    --创建用户test_user。
m_db=# CREATE USER test_user PASSWORD 'XXXXXXXXXX';
-- 修改test_alt1表的所有者为test_user。
m_db=# ALTER TABLE test_schema.test_alt1 OWNER TO test_user;
-- 查看。
m_db=# SELECT tablename, schemaname, tableowner FROM pg_tables WHERE tablename = 'test_alt1';
 tablename | schemaname  | tableowner 
-----------+-------------+------------
 test_alt1 | test_schema | test_user
(1 row)

修改列示例

  • 修改列名 
    -- 建表。
m_db=# CREATE TABLE test_alt2(c1 INT,c2 INT);
-- 修改列名。
m_db=# ALTER TABLE test_alt2 CHANGE COLUMN c1 id INT;
m_db=# ALTER TABLE test_alt2 CHANGE COLUMN c2 areaid INT; 
-- 查看。
m_db=# \d test_alt2
   Table "public.test_alt2"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | 
 areaid | integer |
  • 增加列 
    -- 表test_alt2增加列。
m_db=# ALTER TABLE test_alt2 ADD COLUMN name VARCHAR(20);
-- 查看。
m_db=# \d test_alt2
           Table "public.test_alt1"
  Column  |         Type          | Modifiers 
----------+-----------------------+-----------
 id       | integer               | 
 areacode | integer               | 
 name     | varchar(20)           |
  • 增加AUTO_INCREMENT自增列 
    -- 建表，新增自增列。
m_db=# CREATE TABLE test_autoinc(col1 int);

-- 插入一条数据。
m_db=# INSERT INTO test_autoinc(col1) VALUES(1);

-- 添加一个本地自增列，从1开始自增。
m_db=# ALTER TABLE test_autoinc ADD COLUMN col int AUTO_INCREMENT;

m_db=# SELECT col,col1 FROM test_autoinc ORDER BY 2,1;
 col | col1
-----+------
   1 |    1
(1 row)

-- 将下一个自增值设为10。
m_db=# ALTER TABLE test_autoinc AUTO_INCREMENT = 10;

-- NULL触发自增，自增值为10。
m_db=# INSERT INTO test_autoinc(col, col1) VALUES(NULL,2);

-- 0触发自增，自增值为11。
m_db=# INSERT INTO test_autoinc(col, col1) VALUES(0,3);

m_db=# SELECT col,col1 FROM test_autoinc ORDER BY 2,1;
 col | col1
-----+------
   1 |    1
  10 |    2
  11 |    3
(3 rows)
  • 修改列的数据类型 
    -- 修改test_alt2表中name字段的类型。
m_db=# ALTER TABLE test_alt2 MODIFY name VARCHAR(50);
-- 查看。
m_db=# \d test_alt2
          Table "public.test_alt1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 c1     | integer               | 
 c2     | integer               | 
 name   | varchar(50)           |
  • 删除列 
    -- 删除test_alt2中areaid字段。
m_db=# ALTER TABLE test_alt2 DROP COLUMN areaid;
-- 查看
m_db=# \d test_alt2
          Table "public.test_alt2"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | varchar(50)           |

修改约束示例

  • 修改字段默认值 
    --建表。
m_db=# CREATE TABLE test_alt3(pid INT, areaid CHAR(5), name VARCHAR(20));
--修改test_alt1表中id的默认值。
m_db=# ALTER TABLE test_alt3 ALTER COLUMN areaid SET DEFAULT '00000';
--查看。
m_db=# \d test_alt3
                 Table "public.test_alt3"
 Column |         Type          |        Modifiers        
--------+-----------------------+-------------------------
 pid    | integer               | 
 areaid | char(5)               | default '00000'::bpchar
 name   | varchar(20)           |
    --删除id的默认值。
m_db=# ALTER TABLE test_alt3 ALTER COLUMN areaid DROP DEFAULT;
--查看。
m_db=# \d test_alt3
          Table "public.test_alt3"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 pid    | integer               | 
 areaid | char(5)               | 
 name   | varchar(20)           |
  • 添加表级约束
    直接添加约束 
    --给表添加主键约束。
m_db=# ALTER TABLE test_alt3 ADD CONSTRAINT pk_test3_pid PRIMARY KEY (pid);
--查看。
m_db=# \d test_alt3
          Table "public.test_alt3"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 pid    | integer               | not null
 areaid | integer               | 
 name   | varchar(20)           | 
Indexes:
    "pk_test3_pid" PRIMARY KEY, btree (pid) TABLESPACE pg_default

修改索引示例

删除表中索引 
-- 创建表和索引
m_db=# CREATE TABLE t01(c1 int PRIMARY KEY, c2 int UNIQUE);
m_db=# CREATE INDEX idx1 ON t01(c1);
m_db=# CREATE INDEX idx2 ON t01(c1,c2);
m_db=# \d+ t01
                         Table "public.t01"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 c1     | integer | not null  | plain   |              | 
 c2     | integer |           | plain   |              | 
Indexes:
    "t01_pkey" PRIMARY KEY, ubtree ON t01 (c1) WITH (storage_type=USTORE) TABLESPACE pg_default
    "t01_c2_key" UNIQUE CONSTRAINT, ubtree ON t01 (c2) WITH (storage_type=USTORE) TABLESPACE pg_default
    "idx1" ubtree ON t01 (c1) WITH (storage_type=USTORE) TABLESPACE pg_default
    "idx2" ubtree ON t01 (c1, c2) WITH (storage_type=USTORE) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no, storage_type=USTORE, collate=1537, segment=off

-- 删除表中索引
m_db=# ALTER TABLE t01 DROP INDEX idx1, DROP INDEX idx2;
m_db=# ALTER TABLE t01 DROP INDEX t01_pkey, DROP INDEX t01_c2_key;
m_db=# \d+ t01
                         Table "public.t01"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 c1     | integer | not null  | plain   |              | 
 c2     | integer |           | plain   |              | 
Has OIDs: no
Options: orientation=row, compression=no, storage_type=USTORE, collate=1537, segment=off

m_db=# DROP TABLE t01;

相关链接

CREATE TABLE，DROP TABLE

父主题： A

相关文档