What is the difference between a surrogate key and a natural key?
A natural key is a real-world attribute that is unique, like email or Aadhaar number. A surrogate key is a meaningless generated value, like an auto-increment id. Surrogate keys stay stable when business data changes.
Explain it simply
A natural key is a name written on a school bench: meaningful but it can change if the student moves or corrects a spelling. A surrogate key is the number stamped on the bench by the school. It means nothing, but it never changes.
Example
-- natural key
CREATE TABLE users_a (
email VARCHAR(100) PRIMARY KEY,
name VARCHAR(50)
);
-- surrogate key
CREATE TABLE users_b (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email VARCHAR(100) NOT NULL UNIQUE,
name VARCHAR(50)
);If a user changes email in users_a, every foreign key pointing to it must change too. In users_b, only one column changes.
| Natural | Surrogate | |
|---|---|---|
| Meaning | Real-world | None |
| Stability | May change | Never changes |
| Size | Often long text | Small integer |
| Needs UNIQUE on business data | No | Yes |
Use a natural key when it is truly stable and short, such as a country code. Otherwise a surrogate key with a UNIQUE constraint on the natural one is the common choice.
What to say in the interview
"A natural key comes from the data, a surrogate is generated. Surrogates are small and stable, but I still keep a UNIQUE constraint on the natural identifier so duplicates are not allowed."
Common mistakes
- Using a surrogate key and dropping the unique constraint on email.