Ask the Expert

Creating a random value for the primary key

When we add a new record in the table, how can we insert a random value for the primary key?

    Requires Free Membership to View

There are many ways to do this, all of which have some "cost" associated with them.

The simplest way to do it uses "brute force" from the storage perspective in order to make the process fast and relatively foolproof. First, create a "candidate key" table containing only one column for the primary key. Each time you need a new key, pick one of these keys at random and delete it from the "candidate" key table. It doesn't really matter how random the selection method is, since removing candidates as you use them will effectively "stir" the randomizer with each use. This method has the advantage of being simple to understand, easy to code, and tough to break!

Another method uses similar logic. Each time you need a new key, try to insert a random key value. If the insert fails, try again. This is also simple to code, but it is non-deterministic: you never know how long it will take to find a new key, and it gets slower as more keys get used.

You can improve on the previous idea by using statistical information kept in another table to guide the selection of new keys. This can produce much better results much faster, and it also allows you to "doctor" the key distribution if necessary.

Just as an observation: the use of random keys was once very important in making databases perform well. The problems associated with sequential key generation have been solved in nearly every major database engine. Unless there is some strong reason that requires you to use randomly distributed key values, I'd recommend using something simpler!

For More Information


This was first published in December 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: