Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Auto increment the primary key

514521Sep 20 2006 — edited Sep 20 2006
Is there a way in oracle to set up auto increment e.g., the primary key of a table (number(4)) - start at one, increment by one - during the creation of the schema as some of the other databases do?

I am trying to get away from using "Nextval" (whatever) from within a java application. I am uploading images (blob) and don't always know how many images exist.

Thanks

Comments

JustinCave
If you're familiar with nextval, I assume you are familiar with creating sequences and using sequences to populate primary keys...

You can always create a BEFORE INSERT trigger that automatically generates the primary key for the table. You would have to modify your INSERT statement to use the RETURNING clause if you want the client to have the value of the just-inserted primary key after the INSERT.

Justin
45878
You could create a trigger on the table which performs the "nextval" from a sequence table, that way your Java application does not need to do the work.
519688
use a sequence, and populate it via a "before insert for each row" trigger.

no other way.
514521
Not working..

From w/i SQLPLUS i execute this line:
create trigger dbame.p_trigger before insert on dbame.plant for each row

dbame is my oracle username
plant is the table name

SQLPLUS is looking for more information and/or a closing "tag" (not a semi-colon)

any help would be appreciated..

Thanks
Kamal Kishore
http://download-east.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg13trg.htm#431
519688
read the manuals
look for the section on table triggers
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 18 2006
Added on Sep 20 2006
6 comments
987 views