Wednesday, 5 April 2017

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.

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"