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
- Dozens more answers to tough database design questions from Pat Phelan
- The Best Database Design Web Links: tips, tutorials, scripts, and more
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your database design questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in December 2002