Add a auto increment primary key to existing table in oracle

oracle

mallikarjun · Jul 13, 2012 · Viewed 139.2k times · Source

I want to add a new auto increment primary column to a existing table which has data. How do I do that?

I first added a column and then try to add a sequence after that, I lost how to insert and make that column as primary key.

Answer

Say your table is called t1 and your primary-key is called id
First, create the sequence:

create sequence t1_seq start with 1 increment by 1 nomaxvalue;

Then create a trigger that increments upon insert:

create trigger t1_trigger
before insert on t1
for each row
   begin
     select t1_seq.nextval into :new.id from dual;
   end;