I created a table x with PK x_key_id as NUMBER. I also created a sequence to be able to increment this key.
Now, we have upgraded to 12C and I want to take advantage of oracle's embedded "sequence"
Creating a new table y with the surrogate key as:
x_key_id NUMBER GENERATED AS IDENTITY (START WITH 1 INCREMENT BY 1 NOCACHE) is no problem.
How do I alter the older tables to add the generated as identity on the PK? The only way I can think of is to rename the table, create a new table, move the data into new table and drop the old renamed table. I sure hope there is an easier way to do it. Thanks for any help.
Sadly you can't alter an existing column to become an identity.
Luckily you can get similar behaviour by creating a sequence and making this the default for your column:
create table t (
x int
);
create sequence s;
alter table t modify x default s.nextval;
insert into t values (default);
select * from t;
X
1
Though you can add a new identity column to existing tables:
alter table t add ( y int generated as identity );
insert into t values (default, default);
select * from t;
X Y
1 1
2 2
This assigns a value from the sequence to all existing rows. The method for this is non-deterministic.