Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: