René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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.
Links |