Tech Trained Monkey

Everyday Problem Solvers

Why integers are lousy primary keys

When I was a noob (about 2 years ago) I took a freelance gig to develop a simple system for a small shop. Very basic stuff. Few weeks ago the owner called me saying that he was very happy but he was facing a problem… You see, his business grew and he now has 3 shops, all of them using my system, and he wants to consolidate on one database. Well, as you can imagine I only managed to accomplish this by replacing the primary keys. They were all integers, now they’re GUIDs. The reason? Integers clash very easily! 

GUIDs when generated (and not pulled out of your head) are by definition unique, and so, by logical deduction do not clash, and by another deduction when applied to primary key column, it will not only guarantee the uniqueness of that key in the table, but in the entire universe!

Something that you do have to be carefull about is not to put a clustered on it! You see cluster index define physical order of records and the very nature of a GUID is that it’s random. If you do rest assured that inserts will have a very poor performance.

You must be wondering what I did to successfully operate the transition. Well here it is a basic roadmap:

  1. Foreach integer column that is used as a primary key create a GUID column
  2. run this: Update myTable set myNewGUIDColumn=newid()
  3. on the tables that there’s a FK repeat nº1
  4. run this: Update myOtherTable t1 set myNewFKColumn = (Select myNewGuidCOlumn from myTable t2 where t1.myOriginalPK=t2.myOriginalFK)

That’s it! And of course adapt your code to handle GUIDs instead of integers…

Advertisements

One response to “Why integers are lousy primary keys

  1. Ross December 6, 2012 at 7:52 pm

    “GUIDs when generated (and not pulled out of your head) are by definition unique, and so, by logical deduction do not clash, and by another deduction when applied to primary key column, it will not only guarantee the uniqueness of that key in the table, but in the entire universe!”

    Your logical deduction is wrong. A randomly generated GUID *could* clash, it’s just highly unlikely. It’s also dependent on the system generating the random GUID.

    Regardless of value type, you should be ensuring that keys don’t clash when merging two databases anyway.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: