20

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
Asif Mushtaq
  • 3,658
  • 4
  • 44
  • 80
  • http://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column Hope this should help you. – Nitin Garg Jan 24 '16 at 06:40
  • @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. – Frank Schmitt Jan 24 '16 at 07:17

3 Answers3

30

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

Mycah
  • 4,602
  • 5
  • 24
  • 32
Incognito
  • 2,964
  • 2
  • 27
  • 40
  • what reason could be? and what is the solution now? – Asif Mushtaq Jan 24 '16 at 06:41
  • 1
    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 :) – Incognito Jan 24 '16 at 06:47
  • 1
    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? – Asif Mushtaq Jan 24 '16 at 06:49
  • 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. – Incognito Jan 24 '16 at 06:59
  • 1
    BTW to update the new column, you just execute UPDATE your_Table SET new_column = old_column; – Incognito Jan 24 '16 at 07:02
  • sorry I'm back to ask that Should I use PRIMARY KEY constraint when I already used IDENTITY on COLUMN? – Asif Mushtaq Jan 24 '16 at 12:56
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;
Dmitry Klishev
  • 55
  • 7
0

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);
Shariar Imtiaz
  • 483
  • 1
  • 5
  • 15