Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Column to be modified is not an identity column

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
like image 649
Asif Mushtaq Avatar asked Jan 24 '16 06:01

Asif Mushtaq


People also ask

How do you modify an identity column?

In any case, you cannot modify an identity column, so you have to delete the row and re-add with new identity. You cannot remove the identity property from the column (you would have to remove to column)

How do I change column to identity column?

You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.

How do I change an existing column to an identity in Oracle?

Sadly you can't alter an existing column to become an identity. This assigns a value from the sequence to all existing rows.

What is an identity column in Oracle?

An INTEGER, LONG, or NUMBER column in a table can be defined as an identity column. The system can automatically generate values for the identity column using a sequence generator. See Sequence Generator section. A value for an identity column is generated during an INSERT, UPSERT, or UPDATE statement.


1 Answers

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

like image 122
Incognito Avatar answered Sep 18 '22 07:09

Incognito