Skip to Main Content
  • Questions
  • Modify an attribute to having GENERATED AS IDENTITY

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Nita.

Asked: September 21, 2017 - 1:59 pm UTC

Last updated: September 21, 2017 - 4:49 pm UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

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.

and Chris said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Nita Mann, September 21, 2017 - 5:15 pm UTC

Thank you

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.