Column to be modified is not an identity column

Question

I have created a table with column S_ROLL NUMBER(3) NOT NULL Now I want to make this colum to as identity column. I used this command

alter table students
modify
(
S_ROLL NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
);

Then I'm getting this error.

S_ROLL NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
*
ERROR at line 4:
ORA-30673: column to be modified is not an identity column

Armful

#1049710 Hope this should help you. – Sufi

@NitinGarg This question is about Oracle, but the question you linked to is about MS SQL Server, so I guess it won't help the OP to fix their problem. – Fathom

Answer 1

You're getting this error simply because modifying an existing column as IDENTITY column is not supported right now.

The solution is to add a new column and then drop the existing one (making sure that you do take care of the data too).

Rakish

what reason could be? and what is the solution now? – Armful

Solution is to add a new column and then drop the existing one (obv making sure that you do take care of the data too). I cannot think of a possible reason for this - there must have been some sort of implementation decision taken while releasing this feature. Modifying an existing NOT NULL NUMBER column to IDENTITY would have meant that Oracle needs to compute the highest number in the column and then start the auto increment from there. Probably some sort of difficulty in implementing this in optimal manner? But only people involved can answer the actual reason :) – Rakish

How I can copy the data of existing table to new one? I think copying just single column value could be impossible. then How to copy data of table to new one? – Armful

CREATE TABLE AS new_table AS SELECT * FROM old_table; But, if you just wanted to modify your existing column to IDENTITY column, just add a new column to your existing table and then drop the existing column. You will have to take care of referential constraints, if any. – Rakish

BTW to update the new column, you just execute UPDATE your_Table SET new_column = old_column; – Rakish

sorry I'm back to ask that Should I use PRIMARY KEY constraint when I already used IDENTITY on COLUMN? – Armful

Answer 2

Alternative solution if you don't want to create new column:

CREATE SEQUENCE s_roll_seq
     START WITH 1 -- here last id + 1
   INCREMENT BY 1;

ALTER TABLE students
    MODIFY S_ROLL NUMBER DEFAULT s_roll_seq.NEXTVAL;

Partible

Answer 3

As modifying existing column to identify column is not supported. So you can use below query to add new column.

ALTER TABLE students ADD (S_ROLL_NEW NUMBER(3) GENERATED ALWAYS AS IDENTITY);

Zoila

Can you explain it please? why number_new? – Armful

sorry my mistake, ALTER TABLE students ADD (S_ROLL_NEW NUMBER(3) GENERATED ALWAYS AS IDENTITY); – Zoila

you mean I need new column? then how I can copy the data? – Armful

@UnKnown UPDATE MYTABLE SET NEW_ID = OLD_ID; – Ligneous