Database design: why use an autoincremental field as primary key?

Question

Hi,

here is my question: why should I use autoincremental fields as primary keys on my tables instead of something like UUID values?

What are the main advantages of one over another? What are the problems and strengths of them?

Answer 1

Simple numbers consume less space. UUID values take consume 128 bits each. Working with numbers is also simpler. For most practical purposes 32-bit or 64-bit integers can server well as the primary key. 2⁶⁴ is a very large number.

Consuming less space doesn't just save hard disk space In means faster backups, better performance in joins, and having more real stuff cached in the database server memory.

Answer 2

I guess by UUID you mean like a GUID? GUIDs are better when you will later have to merge tables. For example, if you have local databases spread around the world, they can each generate unique GUIDs for row identifiers. Later the data can be combined into a single database and the id's shouldn't conflict. With an autoincrement in this case you would have to have a composite key where the other half of the key identifies the originating location, or you would have to modify the IDs as you imported data into the master database.

Answer 3

You don't have to use auto-incrementing primary keys, but I do. Here's why.

First, if you're using int's, they're smaller than UUIDs.

Second, it's much easier to query using ints than UUIDs, especially if your primary keys turn up as foreign keys in other tables.

Also, consider the code you'll write in any data access layer. A lot of my constructors take a single id as an int. It's clean, and in a type-safe language like C# - any problems are caught at compile time.

Drawbacks of autoincrementers? Potentially running out of space. I have a table which is at 200M on it's id field at the moment. It'll bust the 2 billion limit in a year if I leave as is.

You could also argue that an autoincrementing id has no intrinsic meaning, but then the same is true of a UUID.