Surrogate Versus Natural Keys
Surrogate keys are system-generated meaningless identifiers with no business semantics; natural keys are real-world attributes that look stable but change, leak, and merge under pressure.
Definition
A surrogate key is an artificial identifier — sequence, UUID, snowflake ID — generated by the system solely for reference. A natural key is a business attribute used as identifier, like email, tax ID, or username. Surrogates insulate relationships from business reality changes; naturals carry meaning but create coupling between schema and mutable reality.
Context and scope
A foundational modeling decision for every entity table. Interacts with clustered index ordering, distributed generation, and external API identifier exposure.
Common misconceptions
- Natural keys that look stable are stable — emails change, tax IDs get reissued, usernames get recycled and renamed
- Surrogate keys hide information — that is the point; business meaning belongs in attributes and constraints, not the join key
- UUIDs are drop-in sequence replacements — random UUIDs fragment clustered indexes; time-ordered variants exist for that reason
Anti-patterns
- Using email or username as the primary key and cascading key updates across the schema when reality changes
- Exposing sequential surrogate IDs in public APIs and leaking competitor volume and enumeration surfaces
- Storing surrogate keys without the unique constraint on the natural identifier, accumulating silent duplicates
Related concepts
Related examples
Authoritative source
What it is
Every row needs an identity to be referenced. The choice is between meaning-free system values and meaningful business values. Surrogates never change when the business reorganizes; naturals document themselves but drag every relationship along when reality moves.
How it works
Sequences give compact, ordered keys that suit clustered storage. Random UUIDs give global uniqueness without coordination but scatter inserts across pages; time-prefixed UUID variants restore locality. Snowflake-style IDs pack timestamp and shard bits for distributed generation.
When to choose
Surrogate primary keys with a unique constraint on the natural identifier is the balanced default: relationships stay stable while uniqueness stays enforced. Pure natural keys suit reference data with genuinely immutable codes.
Pitfalls
Sequential IDs in URLs leak volume and enable enumeration — pair with opaque external identifiers. Surrogates tempt teams into skipping natural-key constraints, letting duplicates grow silently. Mergers and acquisitions stress natural keys hardest: two businesses, one supplier code, different meanings.