Title: ORA-01440: column to be modified must be empty to decrease precision or scale
Tags: oracle
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)?
Comment: Drop the constraint? You might be better off with a CATS+rename, depending on exact circumstances.


Here is another answer: 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);
Comment for this answer: This happens whenever you want to remove a `NOT NULL` property. Generally this is the way to do, as your code looks like: make temporal column, copy everything, drop the original, create the original without the `NOT NULL` property, then copy back, drop the temporal column.
Comment for this answer: adding column syntax is wrong, keyword "COLUMN" is not required while adding a column to a table.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:05/22/14', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<oracle>', 'Title': 'ORA-01440: column to be modified must be empty to decrease precision or scale', 'ViewCount': 27296, 'id': 23802066, 'user_AboutMe': '<p>I love thinking and writing .NET applications and have over 13 years experience + MCPD, MCTS, MCAD, MCP.</p>\n', 'user_AccountId': 45216.0, 'user_DisplayName': 'The Light', 'user_Reputation': 25601.0, 'user_Views': 1093.0, 'user_id': 133212.0}}