Making sure records inserted at the same time are pulled out in the correct order

Using Oracle, how can you ensure that records inserted at the same time (both records have the same date, time...

and primary key in a journal table) are pulled back from the database in the correct order?

Before I answer your question, I'd like to ask one of my own. How can two records have the same primary key? By definition, a primary key uniquely identifies each row in the table. It is impossible for two rows to have the same primary key, so long as you have enable the PRIMARY KEY constraint. With this constraint enabled, it is impossible for two rows to have the same primary key.

One of the ways that people ensure that two records, inserted at the same time, are able to be extracted from the table in insertion order is to add a column to the table. This column will serve as the primary key to the table. And this column will be populated from a SEQUENCE. The sequence will ensure that each row will have a monotonically increasing value. No two rows will have the same sequence value. When extracting from the table, just sort on that sequence value.

For More Information

This was first published in August 2002

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: