Why I use UUIDs for primary/foreign keys

[eluser]dcheslow[/eluser]
Great feedback, thanks!

OK.. so it seems the only disadvantage that I did NOT mention was the readability thing. I concede the point, but...

I'm, personally, against making URLs too editable/readable. If I, as the developer, want the user to be able to go forward or backward one record in the database, then I'll provide a way to do that. The only person who should be hacking URLs is me. A naughty person might be much more tempted to hack /user/123 than /user/BAE7DF4-DDF-3RG-5TY3E3RF456AS10.

Collisions are very (actually... very very) unlikely with a number this large... 16^32 is more than 10 with 37 zeroes after it (number of molecules on earth ~= 10^49). The UUID algorithm includes things like machine ID and current timestamp and some other stuff to ensure uniqueness. If you use a long integer as a key, then you have less than 3 with 6 zeroes after it possible values, if each table of each database of each server starts with 1, then the odds of a collision are pretty good...comparatively speaking.

We shouldn't hard code primary key values anywhere, so making primary keys readable doesn't impact coding at all.

As for library developers only targeting the majority audience... well, if we all coded to the most common browser, then we'd all be using IE <perish the thought>.

gtech raises another significant benefit. He (and I) maintain metadata separately about rows inserted/edited/deleted. I used to have insertedat, insertedby, etc. in every table... but UUIDs make it even simpler. I simply keep one table called 'metadata' which contains recordid,insertedat,insertedby,etc.... Since UUIDs are guaranteed to be unique, I can join to this table with confidence. In fact, UUIDs make all 1:1 relationships totally foolproof.... and 1:M relationships much less likely to screw up.