What is the difference between a natural key and a surrogate key?
Quick Answer
A natural key is a column (or set) that already uniquely identifies a row in the real world — an email, an ISBN, a country code. A surrogate key is a meaningless system-generated value — an int identity or a GUID. Most designs use a surrogate primary key plus a unique constraint on the natural key.
A natural key is something that already identifies the entity in the real world — an email address, an ISBN. A surrogate key is a value the system makes up that has no meaning, like an auto-incrementing int or a GUID. The problem with natural keys as the primary key is they can change — people change email addresses — and if it's a composite natural key it has to be carried into every child table's foreign key. So the standard approach is a surrogate primary key for all the joins and foreign keys, plus a unique constraint on the natural key so you still can't create two rows for the same real-world thing.
- Surrogate pros: never changes (an email might), narrow, simple joins, stable foreign keys.
- Natural key pros: no extra column, the key is meaningful, prevents accidental duplicate business entities.
- Composite natural keys propagate into every child table's FK — a common reason to prefer a surrogate.
- Best of both: surrogate PK for the plumbing,
UNIQUEon the natural key for correctness.