Oracle 12c: How can I modify an existing primary key column to an identity column?

Question

I have a table which contains a primary key column which is auto incremented from application. How can I modify the column to be an identity column in Oracle 12c?

A sample case is provided below-

create table tmp_identity (
   id number(100) primary key,
   value varchar2(100)
);

Say we populated the table with following data-

ID        VALUE
---------------
1         Sample 1
2         Sample 2
3         Sample 3

What we are planning to do is to turn this id column into an identity column which will-

• Auto increment by 1
• Start from 4

How can I do it? If it is not possible, then is there any work-around available for this?

Glorious

Does id number(100) column really have precision 100 ? On Oracle the precision can be from 1 to 38, and no more. – Opheliaophelie

Answer 1

You can't turn an existing column it into a real identity column, but you can get a similar behaviour by using a sequence as the default for the column.

create sequence seq_tmp_identity_id
  start with 4
  increment by 1;

Then use:

alter table tmp_identity 
   modify id 
   default seq_tmp_identity_id.nextval;

to make the column use the sequence as a default value. If you want you can use default on null to overwrite an explicit null value supplied during insert (this is as close as you can get to an identity column)

If you want a real identity column you will need to drop the current id column and then re-add it as an identity column:

alter table tmp_identity drop column id;

alter table tmp_identity 
     add id number(38) 
     generated always as identity;

Note that you shouldn't add the start with 4 in this case so that all rows get a new unique number

Snapback

by fix, i meant getting away from this solution – Chuu

What about other tables referencing the original ids? I @Darek's solution preserves them. – Alloplasm

Be aware that this may COMPLETELY juggle values of the id column!!! If you have any database resources (FKs, checks, ...), then DBMS will stop you. However, if you have any external resources linked via such ID, this may cause mayhem. – Cyzicus

Answer 2

create table tmp_identity (
   ID number(100) primary key,
   value varchar2(100)
);

ID        VALUE
---------------
1         Sample 1
2         Sample 2
3         Sample 3

select max(id)+1 from tmp_identity;

alter table tmp_identity 
add ( ID2  number(100)  GENERATED BY DEFAULT AS IDENTITY INCREMENT BY 1 START WITH 4 NOMINVALUE NOMAXVALUE );

update tmp_identity  set ID2 = ID;
commit;

alter table tmp_identity 
modify ( ID2  number(100)  GENERATED ALWAYS AS IDENTITY INCREMENT BY 1 START WITH 4 NOMINVALUE NOMAXVALUE );

alter table tmp_identity drop column ID;
alter table tmp_identity rename column ID2 to ID;
alter table tmp_identity add constraint primary key ( ID ) ENABLE;

Dingle

This preserves the original IDs. But wouldn't it be "START WITH 4" in the "GENERATED ALWAYS" line? – Alloplasm

This works, but you may need to drop any FKs or other resources referencing the existing id column, and recreate them after the change. – Cyzicus

Answer 3

You can rename the column from id to identity by using following query

ALTER TABLE tmp_identity
RENAME COLUMN id TO identity; 

To auto increment by 1 and start from 4, we can use sequence.

CREATE SEQUENCE identity_incre
MINVALUE 4
START WITH 4
INCREMENT BY 1;

To use the identity_incre sequence in your query

INSERT INTO suppliers
(identity, VALUE)
VALUES
(identity_incre.NEXTVAL, 'sample4');

Daphinedaphna

Renaming a column to identity doesn't make it an "identity" column: – Snapback

What on Earth does renaming a table have to do with an identity column? – Aura