Skip to content
Quantoros

What is a surrogate key vs a natural key?

SQLLevel: IntermediateDatabase Design

What is a surrogate key vs a natural key?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

A natural key has business meaning (email, SSN, ISBN). A surrogate key is artificial — added just to serve as the primary key (e.g., id SERIAL).

Surrogate keys are generally preferred because natural keys can change (emails change), can be long strings (slower indexes), and couple physical storage to business rules.

The tradeoff: natural keys can eliminate JOINs in some queries. Surrogate keys require more lookups but are safer long-term.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions