Auto-incrementing Primary Key

Our doughnut table has a serious issue: we forgot to give it a primary key.

Luckily it contains just a small useless bunch of fake data, so we can happily dump it and rebuild from scratch.

Here is how to destroy (for good!) that table:

drop table doughnut;

Our new doughnut table will have a new shiny column named id that we plan to use as primary key. The issue is that Oracle does not provide the handy autoincrement attribute for a primary key that so many other database make available, so we emulate it, doing it by hand.

Firstly we create a sequence like this one:

create sequence seq_doughnut
increment by 1
start with 1;

Secondly we have to create a trigger, that basically is a function called by the database when an event is, how to say, triggered. This is not so intuitive like the sequence, but it is not too complex either:

create or replace trigger trg_doughnut
before insert on doughnut
for each row
  begin
    if :new.id is null then
      select seq_doughnut.nextval into :new.id from dual;
    end if;
  end;

If no id is passed to the database by the insert statement, a value coming from the sequence is used.