ALTER TABLE [SchemaName.]TableName { ADD [ COLUMN ] ColumnName <DataType> [ <OptColumnCompressInfo> ] [IDENTITY [(seed, increment)]] [DEFAULT <Expression>] [ <ColumnConstraint> ] | ADD <TableConstraint> | ADD PARTITION PartitionName { <RangValueList> | <ListValueList> } [TABLESPACE TablespaceName] | ALTER [ COLUMN ] ColumnName { TYPE <DataType> | SET DEFAULT <Expression> | DROP DEFAULT | SET NOT NULL | DROP NOT NULL | SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } } | DROP [ COLUMN ] ColumnName [ RESTRICT | CASCADE ] | DROP CONSTRAINT ConstraintName [ RESTRICT | CASCADE ] | DROP PARTITION PartitionName | MERGE PARTITIONS PartitionName1, PartitionName2 INTO PARTITION NewPartitionName [TABLESPACE TablespaceName] [ UPDATE GLOBAL INDEXES ] | RENAME [ COLUMN ] ColumnName TO NewColumnName | RENAME TO NewTableName | RENAME PARTITION PartitionName TO NewPartitionName | RENAME SUBPARTITION SubPartitionName TO NewSubPartitionName | SET TABLESPACE TablespaceName | SPLIT PARTITION PartitionName { AT ( <RangValueList> ) | VALUES ( <ListValueList> ) } INTO ( PARTITION partition1 [TABLESPACE TablespaceName], PARTITION partition2 [TABLESPACE TablespaceName] ) [ UPDATE GLOBAL INDEXES ] | TRUNCATE PARTITION PartitionName | TRUNCATE SUBPARTITION SubPartitionName [ UPDATE GLOBAL INDEXES ] | { ENABLE | DISABLE } TRIGGER { TriggerName | ALL | USER } | MODIFY CONSTRAINT ConstraintName { ENABLE | DISABLE } }
功能：
改变表定义,主要可完成下列改变：
-
增加列和表约束
-
增加分区
-
分裂分区
-
修改列默认值
-
修改列名
-
修改分区名/子分区名
-
修改列数据类型
-
修改列数据存储方式
-
更改表所在的表空间
-
删除表约束
-
删除列
-
删除分区
-
截断分区/子分区
-
合并分区
-
启用/禁用表上触发器
-
启用/禁用表上约束
权限：
进行该操作的用户必须是非受限数据库管理员或者是表的拥有者。
使用说明：
-
列约束和表约束的使用请参看本手册CREATE TABLE中有关内容。
-
通过ADD子句可向一已存在于数据库中的表增加列。加入的新列不能与原表中已有的列重名。增加新列的时候，允许设定默认值，但不允许设置not null和primary key ，可以在随后的alter 子句中修改。新加入的表约束应当与原表中已有约束不同。对定义为唯一值或是主码的表约束，原有数据必须满足该约束。其他新加入的表约束只对其后执行的DML命令有效，对原有数据则没有影响。
-
ALTER COLUMN子句用于修改表中已有列的默认值。但不能改变约束，所以，在该子句中，用户不能使用列约束子句。在ALTER子句中，如果有DEFAULT子句，它只对其后执行的DML命令有效，对原有数据无影响。
-
ALTER COLUMN子句用于修改表中已有列的数据类型：
-
当要修改的列中全为 NULL 值或表为空时，不论有没有转换规则，都可以修改数据类型或类型的长度、精度。
-
当该列上有索引表达式的引用，或其他依存关系，或者该列作为分区键时，不能修改数据类型。
-
当该列上设置了缺省值，缺省值的类型和修改后的类型要符合转换规则。
-
当要修改列上有数据（非NULL）时，修改后的类型和原来的类型要兼容，符合转换规则。可以增加类型的长度、精度，但减少类型的长度和精度，或修改成其他类型都可能导致原有数据被破坏或丢失。
-
当列为 numberic 时，只要转换之后的结果不损失整数位，就可以转换，会出现由于转换导致的小数位缺失的现象，所以在转换前要经过评估后现再操作。
-
当列类型为 char 类型时，只要转换之后的列的数据不损失除末尾空格外的数据，就可以转换。
-
当有大对象参与的列类型转换时，当表不满足 (a) 中条件时，则按照转换规则来转换，否则不允许转换。
-
-
不能使用ALTER COLUMN子句给已经存在的列增加identity属性。
-
SET STORAGE子句用于修改表中已有列的数据存储方式。数据列可以存储在行中（最普通的方式），也可以存放在外部（如大数据）。其中，PLAIN指明在行中存储且不压缩固定长度的列，MAIN指明在行中存储列并压缩，EXTERNAL指明在外部存储列但不压缩，EXTENDED指明在外部存储列并压缩。
-
SET TABLESPACE子句用于修改表所在的表空间，不能设置水平分区表的表空间。
-
RENAME子句实现字段的更名。
-
DROP CONSTRAINT 仅限于 check 子句，foreign key 子句和 primary key 子句，not null 约束不能用此语句取消。
-
DROP COLUMN子句用于删除表中的列，CASCADE自动删除依赖于被依赖列或者约束的对象（比如，引用该列的视图）。RESTRICT 如果列或者约束还有任何依赖对象，则拒绝删除该列。这是缺省行为。但是该子句不能删除作为分区键的列。
-
ADD PARTITION 子句只能在已经被水平分区的表中新增一个分区。如果该表不是水平分区表，则不能通过该子句新增一个分区。新增的分区可以有两种类型（范围分区和列表分区），但必须与原分区表的分区类型一致。对于范围分区，该子句只能在分区表的最后一个分区之后新增一个分区，并且新增分区的分区条件必须要大于该分区表最后一个分区的分区条件。新增分区的分区条件可以是常量（或者经过强制类型转换之后的常量），还可以是 MAXVALUE；分区条件必须和分区键一一对应。对于列表分区，新增的分区条件中的值必须唯一，且不得与已有分区条件中的值重复，类型必须与分区键的类型一致或可以隐式转换，可以使用 null 关键字来表示空值，或 default 表示缺省值。如果要在第一个分区之前或者中间的某一个分区位置增加一个新的分区，需要使用SPLIT PARTITION。
-
可以通过 RENAME PARTITION 子句对水平分区表的某个分区重命名，分区的名字不能和水平分区表所在模式下的其它表名，索引名，视图名，分区名等关系名重名。
-
可以通过 DROP PARTITION 子句删除水平分区表中的任何一个分区，对于不同类型的水平分区（哈希分区除外），均可操作。删除分区的同时，分区中的数据也将被全部删除。删除分区时如果水平分区表中只有一个分区，将不会删除该分区，只能通过删除整个水平分区表来删除该分区。
-
可以通过 TRUNCATE PARTITION 子句来截断水平分区表中的任何一个分区，对于不同类型的水平分区，均可操作。截断分区和截断表很类似，都是快速删除其物理数据，同时回收其占据的物理空间。截断分区与删除分区也比较相似，不过截断分区只是删除分区中的数据，而不是删除该分区。
-
可以通过 SPLIT PARTITION 子句来将水平分区表中的任何一个分区分裂成两个新的分区，并删除原分区，只能对两种类型的分区（范围分区和列表分区）操作。分裂分区不会删除分区中原有的数据，因此分裂分区的同时，分区中原有的数据也将被分裂到新的两个分区中。对于范围分区，只能使用AT子句分裂分区。分裂值指定了第一个新分区的分区条件的上限，必须满足小于当前分区的分区条件的上限，同时大于前一个分区的分区条件的上限（如果有的话），第二个新分区的分区条件是当前分区的分区条件的上限。对于列表分区，只能使用VALUES子句分裂分区。分裂值指定了存储在第一个新分区中的所有值，第二个新分区存储当前分区中其他的值。分裂值不能包含当前分区的所有值，也不能包含当前分区中不存在的值。如果分裂的分区是DEFAULT分区，分裂值将作为第一个新分区的分区条件，第二个新分区的分区条件是DEFAULT值。
-
可以通过 MERGE PARTITIONS 子句来将水平分区表中的两个分区合并成一个新的分区，同时删除原来的两个分区，只能对两种类型的分区（范围分区和列表分区）操作。合并分区的同时，两个分区中原有的数据也将被合并到新的分区中。对于范围分区，只能合并两个相邻的分区。要合并的两个分区中下界的分区在前，上界的分区在后。新分区的条件是上界的分区的分区条件。对于列表分区，新分区的分区条件是两个分区的分区条件中的值的合并。如果合并的分区中包含DEFAULT分区，那么新分区的分区条件是DEFAULT值。
-
对于组合分区表，目前不支持 ADD PARTITION，DROP PARTITION，SPLIT PARTITION 和 MERGE PARTITIONS 子句。
-
可以通过 TRUNCATE PARTITION 子句来截断组合分区表中任何一个分区，和单一分区表不同，将会截断该组合分区中所有子分区。
-
可以通过 RENAME PARTITION 子句来对组合分区表中的某个分区重命名。
-
可以通过 TRUNCATE SUBPARTITION 子句来截断任何一个子分区。
-
可以通过 RENAME SUBPARTITION 子句来对某个子分区重命名。
-
-
可以通过{ DISABLE | ENABLE } TRIGGER 子句来启用/禁用表上触发器。TriggerName 要启用或者禁用的单个触发器的名字;ALL 启用或者禁用属于该表的所有触发器。如果这些触发器中有外键约束，那么就要求数据库管理员权限;USER 启用或者禁用所有属于该表的非外键约束触发器.
-
加密列的类型不能修改为大对象类型或复合类型。不能修改加密表中的列为大对象类型或复合类型。
-
不能向表中增加加密列。
-
启用禁用的约束类型仅限于外键约束
-
禁用外键约束后参照表可以使用truncate,但不可以drop。
-
启用已被禁用的外键约束需要检查约束表中的数据，当满足约束条件时约束被启用，否则约束启用失败。
-
KingbaseES分析版支持普通的ALTER TABLE 命令来在一张已存在的表中添加列，并且可以修改表结构，就像对行存储表进行的操作一样。
-
KingbaseES分析版ALTER TABLE命令的语法格式与功能和通用行存储版本完全相同。
示例：
例一：向emp表中增加一个age列。
ALTER TABLE scott.emp ADD COLUMN age INT;
例二：给emp表age列增加一个检查约束：
ALTER TABLE scott.emp ADD CONSTRAINT agechk CHECK ( age < 100 );
例三：删除表emp中的字段age：
ALTER TABLE scott.emp DROP COLUMN age;
例四：向emp表中增加一个age列，缺省值为1。
ALTER TABLE scott.emp ADD COLUMN age INT DEFAULT 1;
例五：增加emp表的ename列的长度（从原来10增加到30个字符）。
ALTER TABLE scott.emp ALTER COLUMN ename TYPE varchar(30);
例六：修改emp表的ename列的类型VARCHAR(30)为CHAR(30)。
ALTER TABLE scott.emp ALTER COLUMN ename TYPE CHAR(30);
CREATE TABLE tbl_coerce_column_type(char_col char(5), numeric_col numeric(5,3), clob_col CLOB, blob_col BLOB);
-- 当表为空表时，类型可以任意转换，不受转换规则的约束
ALTER TABLE tbl_coerce_column_type ALTER COLUMN char_col TYPE BLOB;
ALTER TABLE
ALTER TABLE tbl_coerce_column_type ALTER COLUMN char_col TYPE char(5);
ALTER TABLE
-- 当列值全为NULL时，类型可以任意转换，不受转换规则的约束;
INSERT INTO tbl_coerce_column_type VALUES(NULL, NULL, NULL, NULL);
INSERT INTO tbl_coerce_column_type VALUES(NULL, NULL, NULL, NULL);
ALTER TABLE tbl_coerce_column_type ALTER COLUMN char_col TYPE BLOB;
ALTER TABLE
ALTER TABLE tbl_coerce_column_type ALTER COLUMN char_col TYPE char(5);
ALTER TABLE
-- 列不全为NULL，转换失败
INSERT INTO tbl_coerce_column_type VALUES('abc', NULL, NULL, NULL);
ALTER TABLE tbl_coerce_column_type ALTER COLUMN char_col TYPE BLOB;
ERROR: column "CHAR_COL" cannot be cast to type "BLOB" if column is not NULL
-- 列为numeric
INSERT INTO tbl_coerce_column_type(numeric_col) VALUES (12.123);
-- 丢失小数位
ALTER TABLE tbl_coerce_column_type ALTER COLUMN numeric_col TYPE numeric(5, 2);
TEST=# SELECT * FROM tbl_coerce_column_type;
CHAR_COL | NUMERIC_COL | CLOB_COL | BLOB_COL
----------+-------------+----------+----------
| 12.12 | |
(1 row)
-- 丢失整数位，报错
ALTER TABLE tbl_coerce_column_type ALTER COLUMN numeric_col TYPE numeric(1, 1);
ERROR: numeric field overflow
DETAIL: A field with precision 1, scale 1 must round to an absolute value less than 1.
例七：在分区表employee的最后一个分区之后新增一个范围分区p14：
CREATE TABLE employee (deptno INT, empname VARCHAR(32), grade INT)
PARTITION BY RANGE(deptno)
(
PARTITION p11 VALUES LESS THAN (1000),
PARTITION p12 VALUES LESS THAN (2000),
PARTITION p13 VALUES LESS THAN (3000)
);
ALTER TABLE employee ADD PARTITION p14 VALUES LESS THAN(4000);
例八：删除分区表employee的p12分区：
ALTER TABLE employee DROP PARTITION p12;
例九：重命名分区表employee的分区p11为first_partition：
ALTER TABLE employee RENAME PARTITION p11 TO first_partition;
例十：截断分区表employee的p13分区：
ALTER TABLE employee TRUNCATE PARTITION p13;
例十一：分裂分区表employee的p13分区：
ALTER TABLE employee SPLIT PARTITION p13 AT(2500) INTO
(
PARTITION P13,
PARTITION P14
);
例十二：合并分区表employee的p13分区和p14分区：
ALTER TABLE employee MERGE PARTITIONS p13,p14 INTO PARTITION p13
例十三：在分区表 employee2 中新增一个列表分区 p23：
CREATE TABLE employee2 (id INT, dept char(10))
PARTITION BY LIST(id)
(
PARTITION p21 VALUES (10, 20),
PARTITION p22 VALUES (30, NULL)
);
ALTER TABLE employee2 ADD PARTITION p23 VALUES (40, 50);
例十四：分裂分区表employee2的p21分区：
ALTER TABLE employee2 SPLIT PARTITION p21 VALUES(10) INTO
(
PARTITION P21,
PARTITION P24
);
例十五：合并分区表employee2的p21分区和p24分区：
ALTER TABLE employee2 MERGE PARTITIONS p23,p24 INTO PARTITION p23;
例十六：重命名组合分区表employee的子分区p11为first_subpartition：
CREATE TABLE employee (id INT, dept CHAR(10))
partition by list (id)
subpartition by list (dept)
(
partition p1 VALUES (1, 2)
(
subpartition p11 VALUES ('A', 'B'),
subpartition p12 VALUES ('C', 'D')
),
partition p2 VALUES (default)
(
subpartition p21 VALUES ('A', 'B'),
subpartition p22 VALUES ('C', 'D')
)
);
ALTER TABLE employee RENAME SUBPARTITION p11 TO first_subpartition;
例十七：截断组合分区表employee的p22子分区：
ALTER TABLE employee TRUNCATE SUBPARTITION p22;
例十八：在增加外键时设置NOVALIDATE，可不需检查数据一致性，否则检查
CREATE TABLE t (a int UNIQUE );
INSERT INTO t values(1);
CREATE TABLE s (a int);
INSERT INTO s values(2);
执行下面语句不会报错：
ALTER TABLE s ADD FOREIGN KEY(a) REFERENCES t(a) NOVALIDATE;
例十九：禁用表tab3上的触发器
CREATE TABLE tab3(col int);
INSERT INTO tab3 VALUES(10);
INSERT INTO tab3 VALUES(20);
CREATE TRIGGER trigger3 BEFORE UPDATE OR INSERT ON tab3
FOR EACH ROW
AS
BEGIN
IF new.col > 100 AND new.col < 1000 THEN
new.col := 50;
END IF;
IF new.col > 1000 THEN
RAISE EXCEPTION 'failed';
END IF;
END;
alter table tab3 disable trigger TRIGGER3;
例二十：启用禁用表s上的外键约束
CREATE TABLE t (a INT UNIQUE );
CREATE TABLE s (a INT);
INSERT INTO t VALUES(1);
ALTER TABLE s ADD FOREIGN KEY(a) REFERENCES t(a);
INSERT INTO s values(1);
ALTER TABLE s MODIFY CONSTRAINT s_a_fkey DISABLE;
INSERT INTO s VALUES(2);
--禁用外键约束后参照表可以使用TRUNCATE,但不可以被DROP
TRUNCATE TABLE t;
TRUNCATE TABLE s;
ALTER TABLE s MODIFY CONSTRAINT s_a_fkey ENABLE;
兼容性：
-
在一个表的两列（A, B）上建立一个主键约束，强制删除其中的一列（A）。在 ORACLE 10g 中，在删除这一列的同时删除主键约束。在 KingbaseES 中，同样会删除主键约束，不同的是另一列（B）的 NOT NULL 不会被删除。例子如下：
ORACLE 10g: CREATE TABLE foo (a INT, b INT, CONSTRAINT ab_pk PRIMARY KEY(a, b)); DESC foo; Name Null? Type ------------------------------- A NOT NULL NUMBER(38) B NOT NULL NUMBER(38) ALTER TABLE foo DROP COLUMN a CASCADE CONSTRAINTS; DESC foo; Name Null? Type ------------------------------- B NUMBER(38) KingbaseES: CREATE TABLE foo (a INT, b INT, CONSTRAINT ab_pk PRIMARY KEY(a, b)); Column | Type | Modifiers -------+---------+----------- A | INTEGER | not null B | INTEGER | not null Indexes: "AB_PK_193686" primary key, BTREE (A, B) ALTER TABLE foo DROP COLUMN a CASCADE; Column | Type | Modifiers --------+---------+----------- B | INTEGER | not null
-
当修改表中已有列的数据类型时（修改前后的类型都是 NUMERIC 类型）， 如果表中已有数据并且修改后的精度（或标度）小于修改前的精度（或标度）， 当修改后只会影响到表中已有数据的小数位时，可能会导致小数位的丢失，但不会报错； 当修改后会导致表中已有数据损失整数位时，将会报错。 这和 SQLSERVER 2005 是兼容的。但是和 ORACLE 11g 稍有不同， 在 ORACLE 11g 中，如果表中已有数据，只要减少了精度（或标度），都会报错。 例子如下：
（1）ORACLE 11g： CREATE TABLE foo (a NUMERIC(4,2)); INSERT INTO foo VALUES (12.21); SELECT * FROM foo; 结果：12.21 ALTER TABLE foo MODIFY a NUMERIC(3,0); 结果报错： ALTER TABLE foo MODIFY a NUMERIC(3,0) * 第 1 行出现错误: ORA-01440: 要减小精度或标度, 则要修改的列必须为空 分析：在已有数据的情况下，只要减小了精度或标度，都会报错。 ALTER TABLE foo MODIFY a NUMERIC(1,0); 结果报错： ALTER TABLE foo MODIFY a NUMERIC(1,0) * 第 1 行出现错误: ORA-01440: 要减小精度或标度, 则要修改的列必须为空 分析：在已有数据的情况下，只要减小了精度或标度，都会报错。 （2）SQLSERVER 2005： CREATE TABLE foo (a NUMERIC(4,2)); INSERT INTO foo VALUES (12.21); SELECT a FROM foo; 结果：12.21 ALTER TABLE foo ALTER COLUMN a NUMERIC(3,0); SELECT a FROM foo; 结果：12 分析：只损失了小数位，不会报错。 ALTER TABLE foo ALTER COLUMN a NUMERIC(1,0); 结果报错： 消息 8115，级别 16，状态 8，第 1 行 将 numeric 转换为数据类型 numeric 时出现算术溢出错误。 语句已终止。 分析：当损失整数位时，会报错。 （3）KingbaseES: CREATE TABLE foo (a NUMERIC(4,2)); INSERT INTO foo VALUES (12.21); SELECT a FROM foo; 结果：12.21 ALTER TABLE foo ALTER COLUMN a TYPE NUMERIC(3,0); SELECT a FROM foo; 结果：12 分析：只损失了小数位，不会报错。 ALTER TABLE foo ALTER COLUMN a TYPE NUMERIC(1,0); 结果报错： ERROR: 数值字段溢出 DETAIL: 一个精度为 1，级别为0的字段一定四舍五入到一个绝对值小于10^1. 分析：当损失整数位时，会报错。
245
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
