Auto Increment On A Oracle DB

This week i have a task: explore JHeadstart — Easy — specially if you follow their (uncommented) tutorial. But, when you start to build your own app, things start to get a little confusing (and, difficult) — specially because, by default, the framework creates screens with ids (the user can input whichever id they want) and this is not the right thing on a real-world-app. Ok, but if the user is not going to input the id, we have to create an auto-increment on the column.

Easy. Just put the auto_increment on the column…. No, not.
On Oracle DBs, different of MySQL, you cannot set an column to auto_increment, and it’s not easy to discover how to do it (if you don’t have an oracle dba in the house).

So, god google showed me a link telling me how to do it. If you don’t want to click on the link, i’ll show it for you here.

First, you need to create an sequence — the sequence is the ‘auto-increment-thing’, the one that’ll give to you the numbers.


create sequence funcs_id_seq
start with 1
increment by 1
nomaxvalue;

Then, you will have to create a trigger, that’ll be triggered when you insert an new row into the table:


create trigger func_id_trigger
before insert on functionaries
for each row
begin
select funcs_id_seq.nextval into :new.id from dual;
end;
/

Then, just to an normal insert into your table (w/o the id, of course) and the id will be setted!

Advertisements
This entry was posted in technology and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s