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.
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.
That said, a sequence is guaranteed to give the next consecutive value from its own counter.
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 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 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:
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.