How to get the next number of a table before insertion a record

[Pavan Koppolu]

Hi,
i have a scenario like this, my requirement is i get the next number(primary key) of a table before insertion a new record. I am using oracle database, so there no need to put auto increament option.

I have two possibilities,
1. write a sequence and get sequence.nextval to get the next number
2. Write a java code for getting the next number.
For this, i count the no of the records of the table and increament by 1. But the problem , when multiple users request the same page, there may be a chance to get primary key voilation.

Otherwise, my method is set to be synchorized, but it is a performance problem.


So, please tell me what is the best solution for this scenario.


Thanks
Pavan

[Bear Bibeault]

The problem with the 2nd approach isn't that it's a performance issue, but that it sets up a race condition.

Use a sequence.

[Pavan Koppolu]

First of all, thanks for your reply.

i think use the sequence, there may be a problem like this it dont give the next number of the previous record rather than it gives the some random number of the sequence.

So, please suggest me whether this issue is raised or not for going in the right direction.

Thanks
Pavan

[Bear Bibeault]

Why does it need to be consecutive? As long as it's unique in the table, the value is fine to use as a primary key. If you are using the id values for some other reason than uniquely identifying the record, your data model is screwed up. Primary keys should just be primary keys and serve no other business use.

That said, a sequence is guaranteed to give the next consecutive value from its own counter.

[Martin Vajsar]

Bear Bibeault wrote:That said, a sequence is guaranteed to give the next consecutive value from its own counter.

Not exactly. Sequences are not gap-free and it is not possible to make them so.

Sequences were designed in Oracle to be used together with the RETURNING INTO clause. This way the value can be generated from the sequence, stored into a table and returned to the client in one (JDBC) call. Google up Oracle RETURNING INTO clause if you're interested. I can post an example if someone wants.

[Bear Bibeault]

Not an Oracle user, so I guess you can't even count on that.

But the question remains, why is the actual value of the primary key important? it shouldn't be.

[Martin Vajsar]

Bear Bibeault wrote:But the question remains, why is the actual value of the primary key important? it shouldn't be.

You may need the value of primary key to be able to refer to the new record in the application. You might need it to populate child tables too, though there is the sequence's CURRVAL value for this in Oracle.

[Bear Bibeault]

A primary key should not be used for anything, including ordering. It should be a meaningless unique values whose only purpose is to identify a single record.

[Martin Vajsar]

Bear Bibeault wrote:A primary key should not be used for anything, including ordering. It should be a meaningless unique values whose only purpose is to identify a single record.

I wholeheartedly agree that surrogate primary key values should be meaningless and not used for ordering (natural primary keys are something different, of course).

Often, the record can be created in the database and forgotten about. In this case, value of its PK is irrelevant. However, there are many perfectly legal usages of primary keys of newly created records:

Populating the child tables (already mentioned, though this is not a valid reason in Oracle, it is necessary in other databases).

Putting the newly created record into some application cache.

Writing the newly created record together with its primary key into a log to assist in tracking/debugging.

The newly created record may be displayed in the user interface somehow and the PK may be needed for further user interactions.

These are the most common cases I usually encounter. In all these cases the PK of newly created record needs to be read by the application, and in Oracle, the RETURNING INTO clause is the way to go.

[Bear Bibeault]

I don't disagree with any of that. That's all orthogonal to my point is that expecting the PK to be a specific value for some business reason is a poor practice that will lead to trouble.