Column to be modified is not an identity column

oracle identity-column
Asif Mushtaq picture Asif Mushtaq · Jan 24, 2016 · Viewed 16.2k times · Source

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

Answer

Incognito picture Incognito · Jan 24, 2016

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

Related questions

Inserting into Oracle and retrieving the generated sequence ID

I have a handful of raw SQL queries for SQL Server which use SCOPE_IDENTITY to retrieve the generated ID for a specific INSERT immediately after that INSERT occurs all in one execution… INSERT into Batch( BatchName, BatchType, Source, Area ) …

Read More
ORA-32795: cannot insert into a generated always identity column

Guys I am trying to execute below insert statement and I keep getting the error: cannot insert into a generated always identity column the statement is : INSERT INTO leaves_approval SELECT * FROM requests_temp r WHERE r.civil_number = 33322 AND …

Read More
Oracle identity column and insert into select

Oracle 12 introduced nice feature (which should have been there long ago btw!) - identity columns. So here's a script: CREATE TABLE test ( a INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, b VARCHAR2(10) ); -- Ok INSERT INTO test (b) VALUES ('x'); …

Read More