DB2 Alter Table简介及使用

对于普通表的修改造作，需要考虑下面两种情况：
1.表中有数据
当表中存放着数据的时候，对列的修改仅限于将该列的数据类型修改为相应兼容的数据类型，
或者保持原来的数据类型不变，长度变小了(不能小于存储值的最大长度)，或表长了。
2.表中无数据
列的修改还是只能在相互兼容的数据类型之间，有check约束的列不能修改数据类型。

对于表的修改操作，主要就是增加、修改、删除，下面分别从这三个方面去介绍。
1、增加
增加列：ALTER TABLE tablename ADD COLUMN colname DATATYPE 
        对表的列有如下的一些设置，约束，控制，压缩存储等：
        1.CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | 
                                   CHECK (colname IN (check_list)) DEFAULT list |
                                   REFERENCES table_name (colname)
        2.NOT NULL：非空
        3.COMPRESS SYSTEM DEFAULT：对列指定当前数据类型的默认值，若insert时未提供此列的值的时候；
                                  且压缩存储该列值；
                                  该压缩存储的列，不能为DATE，TIME，TIMESTAMP，XML，或者结构化类型；
                                  需要注意的是，该选项会影响insert和update的性能。

增加约束：
    主键：ALTER TABLE tablename ADD CONSTRAINT constraint_name PRIMARY KEY(colname)。
    外键：ALTER TABLE talename ADD CONSTRAINT constraint_name FOREIGN KEY(colname1) 
          REFERENCES tablename (colname2) 后面跟着：
          1.ON DELETE NO ACTION：当删除colname2的时候，colname1不做任何操作
          2.ON DELETE RESTRICT | CASCADE | SET NULL：但删除colname2的时候，restrict限制不让删除，
                                                  cascade级联删除colname1的行，set null置colname1为空。
          3.ON UPDATE NO ACTION：当colname2被更新的时候，colname1不进行任何操作
          4.ON UPDATE RESTRICT：当有参照完整性的时候，不允许先更新父表。
    唯一：ALTER TABLE tablename ADD CONSTRAINT constraint_name UNIQUE(colnamme)。
    检查：ALTER TABLE tablename ADD CONSTRAINT constraint_name CHECK 后面跟着：
          1.(colname IN (check_list))：限定列的取值为列表中的一个，如sex in ('F','M')。
          2.(colname1 DETERMINED BY colname2)：colname1完全依赖于colname2。
          3.(expression)：此表达式为一个判断，如:alter table test1 add constraint chk_sal check ((salary + comm) > 80000),
                                    对于违反此表达式约束的操作，将会出现如下错误：
                                    db2 => insert into test1 select * from employee order by salary desc
                                    DB21034E  该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。
                                    在SQL 处理期间，它返回：SQL0545N  因为行不满足检查约束"LENOVO.TEST1.CHK_SAL"，所以不允许所请求的操作。SQLSTATE=23513
                db2 => select salary + comm from test1
                1
                ------------
                   156970.00
                    97550.00
                   101310.00
                    83389.00
                    99063.00
                    92130.00
                    88242.00
                  7 条记录已选择。

不能删除表：ALTER TABLE tablename ADD RESTRICT ON DROP
            表不能被删除，表所在的表空间也不可以被删除。

自动生成数据的列：ALTER TABLE tablename ADD COLUMN col_name DATATYPE 后面跟着:
                  1.时间戳：GENERATED ALWAYS|BY DEFAYLT  FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,该列必须为not null。
                  2.表达式：GENERATED ALWAYS expression，通过表达式产生此列的值，如，员工总的工资为：
                                     ... GENERATED ALWAYS (salary + comm)。
                  3.固定值：WITH DEFAULT constant    --常量
                                         datetime-special-register  --特殊寄存器：current_timestamp(current timestamp),current_date(current date),current_time(current time).
                                         user-special-register      --与用户有关的寄存器:current_user(current user),
                                         session_user(session user),system_user(system user).
                                         CURRENT SCHEMA             --当前模式
                                         NULL                                 --空值
                                        EMPTY_CLOB()    --针对CLOB类型的列，写入一个0长度的字符串
                                        EMPTY_DBCLOB()    --针对DBCLOB类型的列，写入一个0长度的字符串
                                        EMPTY_BLOB()    --针对BLOG类型的列，写入一个0长度的字符串
                                       cast_function(str) ★    --针对用户自定义类型，此时DATATYPE为用户自定义类型distinct type；cast_function是自定义类型的名称，
                                                                                主要就涉及基于blob、date、time、timestamp的自定义类型；
                                                                               此处参数str可以为上面提到的寄存器，常量值和CURRENT SCHEMA。
★自动生成的列，不能通过alter table语句修改为序列值，换句话说，序列只能在定义表的时候指定。

db2 => create table test2(id integer generated always as identity,sex char(1) check (sex in ('F','M')))
DB20000I  SQL 命令成功完成。
db2 => alter table test2 add column name varchar(20) with default 'Unknow'
DB20000I  SQL 命令成功完成。
★在表中新增列或者删除列之后，记得重构下表，否则报错：
db2 => insert into test2(sex) values('F')
DB21034E  该命令被当作 SQL 语句来处理，因为它是无效的“命令行处理器”命令。在
SQL 处理期间，它返回：
SQL0668N  不允许对表 "LENOVO.TEST2" 执行操作，原因码为 "7"。   SQLSTATE=57016
db2 => reorg table test2
DB20000I  REORG 命令成功完成。
★
db2 => insert into test2(sex) values('F'),('M')
db2 => select * from test2
ID          SEX NAME
----------- --- --------------------
          1 F   Unknow
          2 M   Unknow
  2 条记录已选择。
db2 => alter table test2 add column col_chg timestamp not null generated always for each row on update as row change timestamp
DB20000I  SQL 命令成功完成。
db2 => select * from test2
ID          SEX NAME                 COL_CHG
----------- --- -------------------- --------------------------
          1 F   Unknow               0001-01-01-00.00.00.000000
          2 M   Unknow               0001-01-01-00.00.00.000000
  2 条记录已选择。
db2 => update test2 set name='Scott' where id=2
DB20000I  SQL 命令成功完成。
db2 => select * from test2
ID          SEX NAME                 COL_CHG
----------- --- -------------------- --------------------------
          1 F   Unknow               0001-01-01-00.00.00.000000
          2 M   Scott                2013-04-01-20.09.27.954000 -- ★
  2 条记录已选择。
db2 => alter table test2 add constraint pk_test2 primary key(id)
DB20000I  SQL 命令成功完成。

2、删除
清空表：ALTER TABLE tablename ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE。
删除列：ALTER TABLE tablename DROP COLUMN column_name CASCADE | RESTRICT
               CASCADE：参照此列的所有视图将会失效，所有依赖于该索引，触发器，函数，约束，都将被删除。
               RESTRICT：若有其他数据库对象依赖于此列，则不允许删除之。
删除约束：
    主键：ALTER TABLE tablename DROP PRIMARY KEY
    外键：ALTER TABLE tablename DROP FOREIGN KEY foreignkey_name
    唯一：ALTER TABLE tablename DROP UNIQUE unique_name
    检查：ALTER TABLE tablename DROP CHECK check_name
    其他约束：ALTER TABLE tablename DROP CONSTRAINT constraint_name
    非空：ALTER TABLE tablename ALTER COLUMN DROP NOT NULL
解除表删除：ALTER TABLE tablename DROP RESTRICT ON DROP。
删除自动生成列的表达式：ALTER TABLE tablename ALTER COLUMN DROP EXPRESSION
                                              针对定义为自动生成值的列。
删除列的默认值：ALTER TABLE tablename ALTER COLUMN DROP DEFAULT
删除表中列的IDENTITY属性：ALTER TABLE tablename ALTER COLUMN DROP IDENTITY
                                                     每个表中只能有一个列被定义为IDENTITY列(非空、不能有默认值)。

3、修改
重命名表：RENAME TABLE tablename old_name to new_name，下面有一篇关于重命名表的文章： DB2修改表名
重命名列：ALTER TABLE tablename RENAME COLUMN old_colname TO new_colname
改变列数据类型：ALTER TABLE tablename ALTER COLUMN col_name SET DATA TYPE data_type
列值压缩存储：ALTER TABLE tablename ALTER COLUMN 后跟着：
              1.COMPRESS SYSTEM DEFAULT：压缩存储，前提是定义表的时候指定VALUES COMPRESSION，如：
                                                                               CREATE TABLE test(column_list) VALUE COMPRESSION。
              2.OFF：对数据不进行压缩存储。
激活或禁用数据压缩：ALTER TABLE tablename ACIVATE | DEACTIVATE VALUE COMPRESSION
自动填充列的值：ALTER TABLE tablename ALTER COLUMN 后面跟着：
                                1.SET GENERATED ALWAYS | BY DEFAULT 后面跟着下面的序列信息：
                                2.SET INCREMENT BY constant
                                   SET NO MINVALUE | MINVALUE constant
                                   SET NO MAXVALUE | MAXVALUE constant
                                   SET NO CYCLE | CYCLE
                                   SET NO CACHE | CACHE
                                   SET NO ORDER | ORDER
                                   RESTART | RESTART WITH constant。
                      这里提到的主要是针对在定义表的时候，指定了增长序列的列，
                     如create table tb (col_1 integer start with 1 increment by 2 no maxvalue no cycle cache 10 no order,...)
                     第二点可以作为独立的选项，如alter table tablename alter column_alter set increment by 10等
                                3.SET EXPRESSION AS expression：修改该自动生成列值的产生表达式。

附加数据：ALTER TABLE tablename APPEND ON|OFF
          ON：新添加的数据将会插入到有空闲空间的页中；
          OFF：新增加的数据存放到最后一个页上，若该页存放满了，则数据将会存放到下一个页上。
          数据的最小存放空间为页(page)，页大小有4K,8K,16K,32K。

★对于UDT类型，alter table不能将该列修改为其他类型，否则会报数据类型不兼容(SQLSTATE=42837)，即使修改为该UDT的基类型。

清空表数据：
实例：
db2 => create table test1 like employee
DB20000I  SQL 命令成功完成。
db2 => select count(*) from test1
1
-----------
          0
  1 条记录已选择。

db2 => insert into test1 select * from employee
DB20000I  SQL 命令成功完成。
db2 => select count(*) from test1
1
-----------
         42
  1 条记录已选择。

db2 => alter table test1 activate not logged initially with empty table
DB20000I  SQL 命令成功完成。
db2 => select count(*) from test1
1
-----------
          0
  1 条记录已选择。
(完善中...)

更多详细信息：DB2 ALTER TABLE

Oracle Alter Table详解

转载请注明出处：http://blog.csdn.net/bobo12082119/article/details/8749494

--the end--