Thursday, October 27

Auto incremental primary keys in Oracle

Why doesn't Oracle support auto incrementing a number column?
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.

3 comments:

sharp said...

Thanks a lot mate, this perfectly works.

sharp said...

Thanks a lot mate, this works perfectly.

villagetalkies said...

Thank you very much for providing important information. All your information is very valuable to me.
Village Talkies a top-quality professional corporate video production company in Bangalore and also best explainer video company in Bangalore & animation video makers in Bangalore, Chennai, India & Maryland, Baltimore, USA provides Corporate & Brand films, Promotional, Marketing videos & Training videos, Product demo videos, Employee videos, Product video explainers, eLearning videos, 2d Animation, 3d Animation, Motion Graphics, Whiteboard Explainer videos Client Testimonial Videos, Video Presentation and more for all start-ups, industries, and corporate companies. From scripting to corporate video production services, explainer & 3d, 2d animation video production , our solutions are customized to your budget, timeline, and to meet the company goals and objectives.
As a best video production company in Bangalore, we produce quality and creative videos to our clients.