Problem solve Get help with specific problems with your technologies, process and projects.

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?

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

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.