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

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
select funcs_id_seq.nextval into from dual;

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

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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s