Surrogate vs Natural Keys
Fast recall
Quick revision
Core answer
Understanding of primary key design choices and their practical trade-offs in real-world applications
Always defaulting to auto-increment integers without considering whether a natural key is more appropriate, or using natural keys that are unstable over time
Discuss the stability problem with natural keys — if the business attribute changes, all foreign keys referencing it must be updated, which can be expensive and error-prone
Interview-ready guide
Interview answer
A surrogate key is a generated identifier with no business meaning, such as an identity number or UUID.
A natural key comes from business data, such as an employee number or ISBN. Surrogate keys are commonly used because they can stay stable while fields such as an email address change. The database should still enforce the natural uniqueness rule with a UNIQUE constraint.
A natural key can be a good choice when it is compact, stable, and truly represents the row—for example, a controlled lookup code with a clear change policy. Neither choice is automatically immutable, and an opaque UUID is not a security control; resource access still needs authorization. The right choice depends on stability, key width, relationship cost, generation, and business meaning.
Surrogate vs Natural Keys
Direct Answer
A surrogate key is a generated identifier with no business meaning, such as an identity number or UUID. A natural key comes from business data, such as an employee number. Surrogate keys are usually chosen because they can remain stable even when business data changes; the database should still enforce any natural uniqueness rules separately.
Code Example
-- Surrogate key: a system-generated id with no business meaning
CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY, -- surrogate
email VARCHAR(255) NOT NULL UNIQUE -- natural value kept unique
);
-- Natural key: use the real-world value directly as the primary key
CREATE TABLE countries (
iso_code CHAR(2) PRIMARY KEY, -- natural, controlled, meaningful
name VARCHAR(100) NOT NULL
);
A surrogate key is useful when natural identifiers might change, such as an email address. A compact natural key can also work when its owner, stability, and change process are clear.
Common Mistakes & Pitfalls
- Email as Primary Key: Email is often a poor key because users can change it; a surrogate id plus a UNIQUE email constraint keeps identity separate from contact data.
- Assuming Any Key Is Immutable: Neither a surrogate nor a natural key is automatically immutable. Treat identity changes as a deliberate, protected operation.
- Treating an Opaque ID as Authorization: A UUID makes guessing harder, but it does not replace an authorization check. Every request must still verify that the caller may access that resource.
Check your understanding
Answer these without looking back at the article.
- When would you prefer a natural key over a surrogate key?
- What are the trade-offs between using UUID versus auto-increment integer as a surrogate key?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.