【赛迪网－it技术报道】在实际的工作和学习中，我们可能由于疏忽将一个字段定义为varchar2类型，后来插入里面的数据又都是数字，在此之后，你会突然发现这个字段确实应该为number类型，而此时如果我们想在不影响用户使用的前提下，或者说尽量小的影响用户，保证数据不丢失的情况下，来更改这个数据类型，这个时候如果我们用普通的alter table modify命令，就可能要遇到错误了。示例如下：

22:25:31 sql> select * from t;

a

----------

10

22:25:32 sql> desc t

名称 是否为空? 类型

-------------------- --------- --------------------

a varchar2(10)

22:25:34 sql> alter table t modify(a number);

alter table t modify(a number)

*

第 1 行出现错误:

ora-01439: 要更改数据类型, 则要修改的列必须为空

其实，类似的现象确实很多，如本来应该为date类型，结果被定义成varchar2类型等等，主要就是那些兼容的数据类型之间的转换定义。当然，为什么一开始会犯这样的错误，那可能原因就有多方面了，如一开始需求就不对等等。

下面，以上面那个例子为基础，介绍两种处理这种问题的方法。

第一种方法，通过增加列来完成

22:25:44 sql> alter table t add (b number);

表已更改。

22:34:16 sql> update t set b = to_number(a)

已更新 1 行。

22:34:39 sql> update t set a = null;

已更新 1 行。

22:34:50 sql> commit;

提交完成。

22:34:52 sql> select * from t;

a b

---------- ----------

10

22:34:55 sql> alter table t modify a number

表已更改。

22:35:21 sql> update t set a=b;

已更新 1 行。

已用时间: 00: 00: 00.01

22:35:33 sql> commit;

提交完成。

22:35:52 sql> alter table t drop column b;

表已更改。

在这种方法中最后也可以先drop column a，然后rename column b to a，达到的效果是一样的。

第二种方法：通过新建表，在新表上更改字段，最后rename新表来完成

22:41:44 sql> create table t1 as select * from t where 1=2;

表已创建。

22:42:14 sql> alter table t1 modify (a number);

表已更改。

22:42:24 sql> insert into t1 select * from t;

已创建 1 行。

22:42:37 sql> commit;

提交完成。

22:42:40 sql> drop table t;

表已删除。

22:42:46 sql> rename t1 to t;

表已重命名。

22:43:00 sql> desc t;

名称 是否为空? 类型

-------------- --------- -------------

a number

我就知道这两种常见的方法，不知道还有没有什么其他的好方法。如果有，请分享。建议使用第一种。

约束和索引都被摧残了，可以做个实验证明一下。

先建立测试表并插入数据

15:28:45 sql> create table t (a varchar2(10) not null);

表已创建。

已用时间: 00: 00: 00.03

15:28:47 sql> insert into t values ('10');

已创建 1 行。

已用时间: 00: 00: 00.00

15:28:55 sql> commit;

提交完成。

已用时间: 00: 00: 00.00

然后增加一个列，并且把数据复制过去

15:29:08 sql> alter table t add (b number);

表已更改。

已用时间: 00: 00: 00.01

15:29:31 sql> update t set b=to_number(a);

已更新 1 行。

已用时间: 00: 00: 00.01

15:29:42 sql> commit;

提交完成。

已用时间: 00: 00: 00.00

15:29:45 sql> alter table t modify b not null;

表已更改。

已用时间: 00: 00: 00.10

在原来的列上建立索引和约束

15:29:59 sql> create index ind_t_a on t(a);

索引已创建。

已用时间: 00: 00: 00.15

15:31:01 sql> alter table t add constraint uk_t_a unique(a);

表已更改。

已用时间: 00: 00: 00.03

斗转星移：

15:31:20 sql> alter table t drop column a;

表已更改。

已用时间: 00: 00: 01.20

15:32:06 sql> alter table t rename column b to a;

表已更改。

已用时间: 00: 00: 00.03

查看索引是否还在？可以看到索引已经被损坏了：

15:33:17 sql> select index_name,status from user_indexes where table_name='t';

未选定行

已用时间: 00: 00: 00.12

15:34:20 sql> select index_name,status from user_indexes order by 1;

index_name status

------------------------------ --------

pk_dept valid

pk_emp valid

已用时间: 00: 00: 00.35

然后查看约束是否还在？可以看到它也被摧残了

15:38:07 sql> select constraint_name,constraint_type,invalid,table_name from user_constraints where table_name='t';

constraint_name constraint_type invalid table_name

------------------------------ -------------------- ------- -----------

sys_c005423 c t

已用时间: 00: 00: 00.03

15:38:24 sql> select constraint_name,constraint_type,invalid,table_name from user_constraints;

constraint_name constraint_type invalid table_name

------------------------------ -------------------- ------- ------------

bin$lnda2w97sk69tbau945o7w==$0 c bin$w9znsgtts9+fs/438tqkua==$0

bin$6jxde6kdtsum1gym3py1eg==$0 c bin$w9znsgtts9+fs/438tqkua==$0

sys_c005423 c t

fk_deptno r emp

pk_dept p dept

pk_emp p emp

已选择6行。

已用时间: 00: 00: 00.23

验证后，唯一约束已经不起作用了

15:38:31 sql> select * from t;

a

----------

10

已用时间: 00: 00: 00.03

15:39:26 sql> desc t;

名称 是否为空? 类型

--------------- --------- ---------------------------

a not null number

15:39:29 sql> insert into t values(10);

已创建 1 行。

已用时间: 00: 00: 00.01

15:39:38 sql> commit;

提交完成。

已用时间: 00: 00: 00.00

15:39:44 sql> select * from t;

a

----------

10

10

可能大家会问，为什么还有一个c约束呢？上面的查询显示它是not null约束：

15:51:06 sql> select table_name,constraint_name,column_name from user_cons_columns where table_name='t';

table_name constraint_name column_name

------------------------------ ------------------------------ -----------------

t sys_c005423 a

16:09:29 sql> select search_condition from user_constraints where table_name='t';

search_condition

--------------------------------------------------------------------------------

"a" is not null