How To Select A Primary Key

In a database, we need to select a primary key for each row. How should we do it?

First, prefer a natural key, which is an intrinsic property of the data, such as the registration number of a car, over a surrogate key, which is a key artificially created for the database and has no meaning outside the database. Why? Because a natural key requires one fewer column, and you have less debate over which one to choose.

Autoincrementing vs UUIDs

If you can’t use a natural key, you have to use a surrogate key. Surrogate keys come in two varieties: auto-incrementing key and UUID.

I don’t have a clear preference for either.

The pros and cons of auto-incrementing keys are:
- Auto-incrementing keys leak information as to how many objects are there in the system, which correlates with the company’s growth.
- Auto-incrementing keys don’t work with a distributed database that well.
- If you want to insert into two tables at once with auto incrementing keys, where the second insert has a reference to the primary key of the first one, you can’t, because you don’t have the key till the insert completed. So it needs more convoluted code.
+ UUIDs are unique across all tables, so we can refer to an object ID without having to specify what kind of data it is. You don’t need a (table name, primary key) tuple.