Surrogate Key Assignment Techniques

Surrogate Key Assignment Techniques
Scott Ambler talks about different techniques for creating primary keys for tables. Primary keys are implemented in many engines as a unique index, even if declared as a table constraint. Without going through all the debate of natural keys versus surrogate keys, I came across a technique in Scott’s book, “Agile Database Techniques”, which I have had for years, and read over and over. But somehow I managed to miss this method of assigning Surrogate keys without using auto number methods internal to a database engine.

Scott calls the technique High/Low in his book. It works like this:

The key values in the table consist of an integer of say 8 digits. The 8 digits are broken into two halves, High/Low (HHHHLLLL).

An external process can now get a range of digits it may assign. It requests from the database the next available high digit (HHHH0000). It will use this number and increment the low digit itself.

Once the number reaches HHHH9999, it requests a new range from the database, starting over again. This reduces the number of round trips required to the database to reserve another range of keys unique to that process.

Of course there is the ability to use sequence generators for different engines already built into them. This technique differs in that it is not engine specific, working with virtually any system requiring sequence assignment from an external source. It has a reduced amount of overhead to obtain uniqueness for each individual process.

There are many other methods including the use of UID, GUID etc. However, I like this High/Low method in that it works with non-relational systems, relational systems, any engine, any hardware platform, and any operating system. It’s pretty hard to find something that universal in software development.

Thanks, Scott, for the tip. By The way, I highly recommend this book.

As always, please send your comments, suggestions, and experiences to btaylor@sswug.org.

Cheers,