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>