What is the difference between a surrogate key and a natural key?
Options
- A surrogate key is artificial (auto id, UUID); a natural key is real data (Correct option)
- They are the same kind of key, but with two different names
- Surrogate keys can never be unique, and natural keys always are
- Natural keys are always numbers, and surrogate keys are text
Answer
A surrogate key is an artificial identifier with no business meaning (auto-increment id, UUID); a natural key is real-world data that identifies a row (email, passport number)
Why
Natural keys come from the data itself, but they can change (people change email) or be reused, and they can be long.
Surrogate keys (id BIGINT GENERATED ... AS IDENTITY, UUID) are generated by the database, are small, stable and never change, which makes foreign keys simple.
Common practice: surrogate primary key, plus a UNIQUE constraint on the natural key.
Remember
Surrogate = artificial stable id. Natural = real-world identifier.