Keys and Synthetic Keys

What a key does

A key uniquely identifies each entity instance within an entity set. Given a key value, there is at most one entity with that value. This property is what makes keys the foundation of data integrity: they give us a reliable way to refer to a specific row.

Natural keys

A natural key is a key formed from attributes that have real-world meaning — a National Insurance number, an email address, a vehicle registration plate, an ISBN. They seem appealing because they already exist and users already understand them.

However, natural keys are dangerous for several reasons:

Problem	Example
Reassignment	A dissolved company’s VAT number may be reissued to a new entity after several years.
Mutability	A person changes their email address; an ISBN is reissued for a new edition.
Non-universality	Not every person has a passport number; not every product has a barcode.
Format evolution	Telephone numbering plans change; postcode formats are revised.
Out of your control	You do not administer NI numbers or domain names. A policy change at the issuing authority can break your schema.
Privacy concerns	Using an email address or NI number as a primary key exposes it in every foreign key reference, in every join, in every log file.

Once a natural key is embedded throughout a schema as foreign keys, changing it requires cascading updates to every referencing table — an operation that may be slow, dangerous under concurrent access, or impossible if the database lacks ON UPDATE CASCADE.

Synthetic keys (surrogate keys)

A synthetic key (surrogate key) is an identifier generated by the database system at insertion time. It has no meaning outside the database and is never exposed to end users.

Common implementations:

Method	Storage	Properties
SERIAL / AUTO_INCREMENT	4 or 8 bytes	Sequential, predictable, fast for B-tree insertion
UUID (v4)	16 bytes	Random, globally unique, no coordination needed across distributed systems
UUID (v7)	16 bytes	Time-ordered prefix, good for index locality

The synthetic key approach has clear advantages:

• Stable: it never changes once assigned.
• Guaranteed unique: the DBMS enforces uniqueness.
• Under your control: you define the generation policy, not an external agency.
• Simple: often just an integer, making foreign keys compact and joins fast.

The only safe approach for large, evolving systems is to use synthetic keys for all primary keys. This is not dogmatic but practical: decades of operational experience show that natural keys eventually cause problems, and the cost of fixing them is far higher than the small upfront cost of adding a surrogate key.

Natural keys still need constraints

Using a synthetic primary key does not mean you abandon natural key constraints. A table of employees might have:

employee_id  SERIAL PRIMARY KEY,    -- synthetic
ni_number    TEXT UNIQUE NOT NULL   -- natural, still enforced

The UNIQUE constraint on ni_number ensures that business logic consistency is maintained — no two employees can share an NI number — but ni_number is not the primary key. If an NI number changes, only one column in one row needs updating, rather than every foreign key reference across the entire database.

Formal definition (relational model preview)

Let $R(X)$ be a relational schema, where $X$ is the set of attributes of $R$. Let $Z \subseteq X$ be a subset of attributes.

$Z$ is a superkey for $R$ if, for any records $u$ and $v$ in any valid instance of $R$:

$u[Z] = v[Z] \implies u[X] = v[X]$

In other words: if two records agree on all attributes in $Z$, they must be the same record (agree on all attributes in $X$).

$Z$ is a key for $R$ if:

1. $Z$ is a superkey for $R$, and
2. No proper subset of $Z$ is a superkey for $R$.

The second condition is minimality: you cannot drop any attribute from the key and still have a superkey. Every relation has at least one key (in the worst case, the set of all attributes $X$ is a superkey, and if no proper subset is a superkey, then $X$ itself is a key). One key is typically designated as the primary key for implementation purposes.

Summary

• A key guarantees that each entity instance can be uniquely identified.
• Natural keys are appealing but fragile: they are mutable, may not be universal, and lie outside the database’s control.
• Synthetic keys are stable, guaranteed unique, and under your control — the safe default for any non-trivial system.
• Natural keys should still carry UNIQUE constraints to enforce business rules.
• Formally, a superkey is a set of attributes that functionally determines all attributes; a key is a minimal superkey.