Two Ways to Identify a Row
A natural key identifies a row using real world data that is already unique, like an email or a passport number. A surrogate key is a meaningless value the database generates, such as an auto incrementing number or a random identifier.
Why Surrogate Keys Are Popular
- They never change, even if the underlying real world value does.
- They are compact and fast as join keys.
- They avoid exposing sensitive real world data in foreign keys everywhere.
Where Natural Keys Shine
- They carry meaning, so a row is recognizable without a join.
- They prevent duplicates by enforcing real world uniqueness directly.
The Usual Compromise
Many designs use a surrogate key as the primary key for stable joins, while also placing a unique constraint on the natural key to enforce real world uniqueness. This gets stability and integrity at once.
Key idea
A surrogate key gives stable meaningless identifiers for joins while a unique constraint on the natural key enforces real world uniqueness, so using both captures the strengths of each.