UUIDs As Keys?

We all use BIGINT or it's ilk as the "primary key" of a table. In MySQL's AUTO_INCREMENT or PostgreSQL and Oracle's sequences to generate these unique numbers across INSERT statements.

What if we used UUIDs as primary keys? There are numerous UUID generators out there. Java has a couple of classes (UID and VMID) buried down in the RMI package that are good for this exact job.

I got this idea from a blog entry by Jason Carrerira concerning the hashCode() in Hibernate. The executive summary of benefits to this idea are:

• The 'id' of the object is known at the time the object is created, giving you a reliable way to perform an equals() or hashCode(), rather than building it from unique combinations of object properties.


• No querying the database for the id of the insert, which saves a query.


• Easy uniqeness, even across tables and replication. Generating unique ids across an application could enable master-master replication!


• Users can't just swap in a number to see what happens. I don't agree with this one, because the ones I've seen java generate are predictable.
  

I'm currently generated 4,000,000 unique ids and an accompany random string for each one to insert into a table. We will then create a table with an AUTO_INCREMENT field with the same string value and test the speed. We'll see what MySQL thinks of this idea.

Comments

[Nick Bastin]

...maybe no one uses them for primary keys because that'd be slow. And by the way, if it's not slower than using an int, you gotta question how they implemented integer primary keys.