DB2修改唯一性索引(Unix Index)所在列字段类型时,SQL0478N

问题描述:

1.) DB2中，如果某一列上有唯一性索引，那么当修改这个列的字段类型的时候，会报SQL0478N的错误，例如，将表T2的ID列由int型修改为varchar型。

$ db2 "create table t2(id int not null, age int not null)"

$ db2 "create unique index idx21 on t2(id)"

$ db2 "insert into t2 values(1100,23)"

$ db2 "alter table t2 alter column id set data type varchar(64)"

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0478N  The statement failed because one or more dependencies exist on the 
target object.  Target object type: "COLUMN". Name of an object that is 
dependent on the target object: "IDX21". Type of object that is dependent on 
the target object: "INDEX".  SQLSTATE=42893

2.) 如果索引不是唯一性索引，则修改没有任何报错。

问题解决：

尝试测试唯一性约束，发现有类似的报错：

db2 "create table t3(id int not null, age int not null)"

db2 "alter table t3 add constraint cons1 unique (id)"

db2 "alter table t3 alter column id set data type varchar(64)"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0478N  The statement failed because one or more dependencies exist on the 
target object.  Target object type: "COLUMN". Name of an object that is 
dependent on the target object: "CONS1". Type of object that is dependent on 
the target object: "UNIQUE CONSTRAINT".  SQLSTATE=42893

解释如下，原因是唯一性约束不能被修改，若想修改，只能重建:
Creating and modifying unique constraints, To modify this constraint, you would have to drop it, and then re-create it.

http://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/t0020167.html