ORA-01440: column to be modified must be empty to decrease precision or scale

Question

I have an Oracle table called Products. It has a ID column with the type of NUMBER.

I'd like to change its type to Number(20, 0) but it's giving me this error:

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

which is reasonable as it's a not nullable primary key.

How to change its datatype from Number to Number(20, 0)?

Answer 1

check whether ID IS A PRIMARY KEY. If yes , you cannot modify it to nullable or insert NULL value to it..

so its better to do the following after doing '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 :

 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);