Why I use UUIDs for primary/foreign keys

[eluser]dcheslow[/eluser]
This is sort of a MySQL topic, but I am posting it here because many CI libraries and classes ASSUME that primary keys are always integers. Mine are not and it makes some libraries unusable for me.

Why I use UUIDs for primary/foreign keys

I don't mean to start a religious war, but I've noticed that many developers still use AutoIncrement int fields as primary/foreign keys. I find this curious because I stopped using integers as keys a couple years ago and have never regretted it. Perhaps you didn't even know that there is an alternative.

Rather than integer primary key fields I use UUIDs (universally unique IDs). A UUID is a 36 character hexadecimal string with 4 embedded dashes...which means that it represents 16^32 possible values (a very big number, by any measure). UUID generators use a special algorithm to ensure that it is extremely unlikely that a collision ever occurs. You can treat UUIDs as disposable... you'll never run out (certainly not in the lifetime of your application), and no two will ever be the same.

The easiest way to generate a UUID is to let MySQL do it for you:

SELECT UUID();

The main advantage to using UUIDs is that they are not just unique within a table (like AutoIncrement) but within a database... and even across databases. This is great whenever you combine data from different tables or databases. For example, when using UNION, merging two tables into one or restoring a partial backup.

Another reason you might want to use UUIDs is that, because they are strings, they are usable as file/folder names and javascript ids. I use this feature often when caching results, uploading files, generating temporary files or building forms. One little 'gotcha' that happened to me once... when passing UUIDs as parameters of javascript functions, be sure to quote them. If the UUID up to the first dash just happens to be all digits, then javascript will try to perform subtraction unless it's quoted. That's a pretty hard bug to find. You should quote all function string parameters anyway, so this is not really a burden, just a reminder not to get sloppy.

The main disadvantages to using UUIDs are performance and disk/memory space. If you're working with terabytes of data, then integers are definitely a better way to go. If you're building a batch processing system, then by all means use integers. But almost all of what I've been programming lately is transaction based, where an extra dozen milliseconds or 20 more bytes of disk space here or there isn't ever noticed.

There is really no reason that a UUID must be stored as a 36 character VARCHAR... it could be stored in a 12 byte unsigned integer - which is what it really is. To keep them useful as file/folder/id values, there needs to be an (automatic?) conversion to/from hexadecimal. Several people have posted PHP solutions like this, but it really needs to be in the database code to be efficient. Truly effective support for this would require the introduction of a new datatype in SQL which could happen but I'm not holding my breath.

One final note, you may see UUIDs referred to as GUIDs... for all intents and purposes, they are equivalent.

So... there you have it. I hope this makes you think about switching from integer to UUID primary key fields. I really like them. If you're not convinced to use them yourself, then I hope you will at least consider that not everyone uses integers as primary keys when you are programming your libraries.

=dave=