The logical way to do this would be to allow a sequence to be tied to a column default value.
This "should" work.
create table itest (
id number(9) default seq_itest_id.nextval primary key,
name varchar2(10) not null,
age number(2)
);
But it doesn't. The concept exist in this way in Postgres (using a sequence, it can even do all the association itself if you specify datatype 'serial'. MySQL has its' own quite odd way to this, simply just "auto increment" the field without any real flexibility or control... the typical MySQL approach ;)
I've been helping a friend port a MySQL schema/app to Oracle without needing to change the app code to much, so auto incremental id's was a must.
This is the way to do it, using a very simple trigger.
drop table itest;
create table itest (
id number(9) primary key,
name varchar2(10) not null,
age number(2)
);
drop sequence seq_itest_id;
create sequence seq_itest_id;
drop trigger itest_id_trigger;
create trigger itest_id_trigger
before insert on itest
for each row
begin
select seq_itest_id.nextval into :new.id from dual;
end;
/
insert into itest(name,age) values('Scott',25);
insert into itest(name,age) values('Sarah',24);
insert into itest(name,age) values('Billy',33);
-- Ok, lets see what we got in our table
select * from itest;
ID NAME AGE
---------- ---------- ----------
1 Scott 25
2 Sarah 24
3 Billy 33
Not to difficult.
