Temporarily Disable a Generated Always As Identity Column
Oracle Database 12c allows you to define a column to be an IDENTITY column.
It can be either GENERATED ALWAYS or BY DEFAULT.
A GENERATED ALWAYS AS IDENTITY column does not allow you to INSERT a value for it.
When it comes to moving data from one table to another and this type of column is present, there are a few steps to "disable" the GENERATED ALWAYS AS IDENTITY column and after the data move "enable" it.
I may write a SQLcl Alias to perform a GENERATED ALWAYS AS IDENTITY "disable" and "enable"
It can be either GENERATED ALWAYS or BY DEFAULT.
A GENERATED ALWAYS AS IDENTITY column does not allow you to INSERT a value for it.
When it comes to moving data from one table to another and this type of column is present, there are a few steps to "disable" the GENERATED ALWAYS AS IDENTITY column and after the data move "enable" it.
set echo on drop table test_identity; create table test_identity(id number generated always as identity); --This insert will fail as the column is generated always --and it does not accept values insert into test_identity(id) values(1); --Change the identity column to generated by default. --This will allow us to insert our values alter table test_identity modify id generated by default as identity; --This insert works. as will Copy to Oracle insert into test_identity(id) values(1); select * from test_identity; --Change the identity column back to generated always. --And set its start value to the max id in the table + 1. alter table test_identity modify id generated always as identity (START WITH limit value) ; --test this fails as expected because the identity is back to generated always insert into test_identity(id) values(1); --test that the identity generates a value after the values inserted --and not a duplicate. so the table should have two rows 1 and 2. insert into test_identity(id) values(DEFAULT); select * from test_identity;
I may write a SQLcl Alias to perform a GENERATED ALWAYS AS IDENTITY "disable" and "enable"