Autoincrement primary key for Oracle

create sequence test_seq

start with 1

increment by 1

nomaxvalue;



create trigger test_trigger

before insert on test

for each row

begin

select test_seq.nextval into :new.id from dual;

end;



insert into test values(test_seq.nextval, 'voila!');



select sequence_name from user_sequences;

select trigger_name from user_triggers;

drop sequence test_seq;

drop trigger test_trigger;

alter trigger test_trigger disable;

alter trigger test_trigger enable;



Note:

a sequence is used to generate a primary key/unique value. Nothing more, nothing

less.  any assumptions about "gap free" or "lost numbers" is in vain.



things in the shared pool (where sequences are cached) are aged out using an LRU

(least recently used). Hence, if you select s.nextval and then don't touch S for

a while and we need space in the shared pool -- out goes S and out goes any

cached values for S.