Every table in a relational database should have a primary key: a column, or a set of columns, that uniquely identifies each row and can never be empty. The question that almost always comes up during design is whether to use a value that already exists in the real world, such as a tax code or an email address, or an identifier created by the database for that purpose. The choice has consequences that are often discovered years later.
Two families of keys
A natural key is an attribute that already has meaning outside the database: a person's tax code, a company's VAT number, a book's ISBN, a vehicle's licence plate, an account's IBAN.
A surrogate key is a value with no meaning, generated by the system solely to identify the row: an auto-incrementing integer (1, 2, 3…) or a universally unique identifier (UUID).
The advantages of natural keys
Natural keys have an obvious appeal:
- no extra column is needed, because the value has to be stored anyway;
- they prevent duplicates by themselves: two rows with the same tax code are rejected;
- they make related tables readable, because in the orders table you can immediately see who each order belongs to.
The problems with natural keys
The difficulty is that real-world data changes more often than you think, and a primary key should stay stable forever.
The value can change
People change email addresses all the time. Even a tax code, which looks immutable, can be corrected if it was calculated from wrong data. When a key changes, the change must be propagated to every table that uses it as a reference: possible, but risky and slow on large volumes of data.
The value can be missing
A foreign customer may not have an Italian tax code. A company being set up may not yet have a VAT number. If the key is mandatory, you cannot record that party, or people will invent placeholder values that pollute the data.
The value may not really be unique
Many «unique» identifiers are unique only in theory. Italian tax codes have cases of collision between different people, resolved through a dedicated mechanism, and not every system handles it correctly. Identifiers assigned by third parties can also be reassigned over time.
The value may be personal data
Using a tax code as a key means spreading it into every related table, into logs, URLs and export files. Every copy is one more piece of personal data to protect.
The advantages of surrogate keys
A surrogate key has none of these problems: it never changes, it exists from the moment the row is created, it is unique by construction and it reveals nothing. It is also compact: an integer takes only a few bytes, and indexes and relationships built on it are fast.
CREATE TABLE customers (
customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tax_code CHAR(16) UNIQUE,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL
);
In this example the primary key is surrogate, but the tax code and email are still protected by a UNIQUE constraint. This is the most important point: a surrogate key does not remove the need for uniqueness constraints on real data. Without the constraint, nothing stops the same customer from being inserted twice with two different ids.
Auto-increment integers or UUIDs
There are two main choices for surrogate keys.
| Aspect | Auto-increment integer | UUID |
|---|---|---|
| Size | 4 or 8 bytes | 16 bytes |
| Readability | Easy to read out and compare | Long and awkward to read |
| Generation | By the database, in sequence | Anywhere, even offline or in the application |
| Predictability | The next id can be guessed | Not predictable |
| Merging different databases | Collisions likely | Collisions practically impossible |
An auto-increment integer is the simplest choice for most applications. UUIDs are useful when several systems create records independently, when data from different sources has to be merged, or when the id appears in a web address and you do not want anyone to browse records by changing a number. Bear in mind, though, that hiding the id is no substitute for permission checks: every request must still verify that the user is allowed to see that record.
Fully random UUIDs, inserted into an ordered index, cause writes scattered across different parts of the index. Time-ordered versions, such as version 7 UUIDs, reduce this problem.
When a natural key is fine
There are cases where a natural key is the most sensible choice:
- stable code tables, such as ISO country or currency codes, which rarely change and are standard;
- junction tables in many-to-many relationships, where the key made of the two foreign keys is natural and sufficient;
- internal data whose code is decided and controlled by the organisation itself and will never be reassigned.
In short
- A primary key must be unique, always present and stable over time.
- Natural keys are convenient but can change, be missing or contain personal data.
- Surrogate keys are stable and compact, but must be paired with
UNIQUEconstraints on real data. - Integers for most cases, UUIDs for distributed systems or exposed ids.
- Natural keys remain suitable for standard code tables and junction tables.
