Thursday, May 21, 2015

Add a auto increment primary key to existing table in oracle

Suppose table1 is the table and its primary key is SLNO
First, create the sequence:
create sequence  table1_seq start with 1 increment by 1 nomaxvalue; 
Then create a trigger that increment upon insert:
create trigger table1_trigger
before insert on table1

for each row
   begin
     select table1_seq .nextval into :new.slno from dual;   end;

No comments: