Oracle and autonumber

Oracle does not have an autonumber like SQLServer has. Oracle's way to for autonumbers is using sequences and triggers. The sequence makes sure that unique numbers are generated while the trigger fills the these numbers into the column for which an autonumber is desired. This is demonstrated in the following.

I need a table with a autonumber column. In this case, it will be column id.

create table tbl_autonumber(
  id     number        primary key,
  txt    varchar2(20)
);

Then, the sequence:

create sequence seq_autonumber;

And the trigger. It fires whenever a new record is inserted and assigns the sequence's next value (using nextval) to :new.id.

create trigger trg_autonumber
before insert on tbl_autonumber
for each row
begin
  select seq_autonumber.nextval into :new.id from dual;
end;
/

Now, three records are inserted:

insert into tbl_autonumber (txt) values('bar');
insert into tbl_autonumber (txt) values('baz');
insert into tbl_autonumber (txt) values('qqq');

select * from tbl_autonumber;

The trigger did its job and filled id with unique values:

        ID TXT
         1 bar
         2 baz
         3 qqq

Now, trying to circumvent the 'autonumber' and specify an aribtrary id:

insert into tbl_autonumber (id, txt) values(100, '###');
insert into tbl_autonumber (id, txt) values(200, '???');
insert into tbl_autonumber (id, txt) values(300, '!!!');

Doesn't work, is overwritten by trigger!

select * from tbl_autonumber;

        ID TXT
---------- -------------------------
         1 bar
         2 baz
         3 qqq
         4 ###
         5 ???
         6 !!!

Changing the trigger and ...

create or replace trigger trg_autonumber
before insert on tbl_autonumber
for each row
begin
  if :new.id is null then
    select seq_autonumber.nextval into :new.id from dual;
  end if;
end;
/

.... trying again to overrule the trigger:

insert into tbl_autonumber (id, txt) values(111, 'This');
insert into tbl_autonumber (id, txt) values(222, 'should');
insert into tbl_autonumber (id, txt) values(333, 'work');

And indeed, it worked. However....

select * from tbl_autonumber;

        ID TXT
---------- --------------------------------------
         1 bar
         2 baz
         3 qqq
         4 ###
         5 ???
         6 !!!
       111 This
       222 should
       333 work

.... it should be clear that it is a bad idea to supply primary keys if there is a sequence.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger

Oracle and autonumber

Links