Skip to content
Advertisement

Modify generated column in Oracle

I have created a database table in Oracle with an auto generated column:

ID NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1)

Now I do not need it to be generated automatically. I tried to write:

ALTER TABLE table_name MODIFY id number;

But it did not work. The database gives an error:

32795.0000 -  "cannot insert into a generated always identity column"

Can someone please explain how to solve this issue without dropping the column?

Advertisement

Answer

(There’s no such thing as “PLSQL database”; PL/SQL is a procedural extension to Oracle’s SQL. Database is Oracle.)


Drop the identity:

SQL> create table test
  2    (id number generated always as identity (start with 1 increment by 1));

Table created.

SQL> alter table test modify id drop identity;

Table altered.

SQL>
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement