oracle ORA-01440: 要修改的列必须为空以降低精度或小数位数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23802066/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01440: column to be modified must be empty to decrease precision or scale
提问by The Light
I have an Oracle table called Products. It has a ID column with the type of NUMBER.
我有一个名为 Products 的 Oracle 表。它有一个类型为 的 ID 列NUMBER。
I'd like to change its type to Number(20, 0)but it's giving me this error:
我想将其类型更改为，Number(20, 0)但它给了我这个错误：
ORA-01440: column to be modified must be empty to decrease precision or scale
ORA-01440: column to be modified must be empty to decrease precision or scale
So I've used this script:
所以我使用了这个脚本：
alter table Products add ID_TEMP NUMBER(20,0);
update Products set ID_TEMP = ID;
update Products set ID = NULL;
alter table Products modify ID NUMBER(20,0);
update Products set ID = ID_TEMP;
alter table Products drop column ID_TEMP;
But it complains that
但它抱怨说
cannot update ID to NULL
cannot update ID to NULL
which is reasonable as it's a not nullable primary key.
这是合理的，因为它是一个不可为空的主键。
How to change its datatype from Numberto Number(20, 0)?
如何将其数据类型从 更改Number为Number(20, 0)？
回答by Dileep KK
check whether ID IS A PRIMARY KEY. If yes , you cannot modify it to nullable or insert NULL value to it..
检查 ID 是否为主键。如果是，则不能将其修改为可为空或向其插入 NULL 值。
so its better to do the following after doing 'UPDATE ID_TEMP WITH VALUES OF ID'
所以最好在执行“UPDATE ID_TEMP WITH VALUES OF ID”后执行以下操作
DROP THE ID COLUMN, AND ITS PRIMARY KEY CONSTRAINT. RENAME THE COLUMN ID_TEMP TO ID Then set the ID column to primary key. Example below :
删除 ID 列及其主键约束。RENAME THE COLUMN ID_TEMP TO ID 然后将 ID 列设置为主键。下面的例子：
ALTER TABLE Products ADD COLUMN ID_TEMP NUMBER(20,0);
UPDATE PRODUCTS SET ID_TEMP = ID;
ALTER TABLE PRODUCTS DROP COLUMN ID;
ALTER TABLE PRODUCTS DROP CONSTRAINT ID_PK;
RENAME COLUMN PRODUCTS.ID_TEMP TO ID;
ALTER TABLE PRODUCTS ADD CONSTRAINT ID_PK PRIMARY KEY (ID);
