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.