Natural Key vs. Surrogate Key

Level 5 — Table Relationships & JOINs The fundamental design choice in schema design comparing natural identifiers (real-world values like email or ISBN) with artificial identifiers (generated values like IDs or UUIDs) for primary keys.

1. Prerequisites

• PRIMARY KEY — The unique row identifier targeted by relationships.

2. Term Category

Schema Design (Primary Key Strategy Comparison): Natural vs Surrogate Keys compares business domain attributes (e.g. SSN, ISBN) against artificial sequence/UUID primary keys.

3. Explanation

Environment Context

• Universal Standard (A core architectural design principle applicable across all relational database systems).

(1) Design Motivation — "Why did we design this?"

Every table in a relational database needs a Primary Key to uniquely identify rows and establish foreign key relationships.

When creating a table, database designers must choose between two approaches:

1. Natural Key

A primary key selected from data columns that already exist in the real world and are naturally unique:

• A user's email_address.
• A book's isbn_code.
• A citizen's social_security_number.

2. Surrogate Key

An artificial primary key generated by the database engine that has no real-world business meaning and exists solely to identify rows:

• An auto-incrementing integer (id).
• A randomly generated string (uuid).

(2) The Trade-offs (Why Surrogate Keys Win)

While natural keys look appealing because they save a column, they carry severe structural risks:

• Real-world identifiers change: If a user changes their email address, you must run slow update queries to cascade that string change to every foreign key column in every child table in the database. If you use a surrogate integer ID, the email can change a thousand times, but the integer ID remains static, meaning relationships never break.
• Performance (Indexing & Joining): Relational databases join tables by comparing keys. Comparing a 4-byte integer (id) is dramatically faster and consumes less index memory than comparing a 50-character email string (email).

(3) The Best Practice Standard

Modern database design uses a hybrid approach:

1. Use a Surrogate Key (like an auto-incrementing identity) as the table's official PRIMARY KEY. This key is used for all internal database joins and foreign keys.
2. Use Unique Constraints on your natural keys (like email or ISBN) to enforce real-world business rules.

(4) Reality Metaphor

Imagine a massive package warehouse:

• Natural Key: Identifying packages by the written address printed on the box. It is unique to that package. However, if the customer calls to change the delivery address mid-transit, workers have to search the warehouse, scrape off the old address label, and write a new one, breaking catalog sorting.
• Surrogate Key: The warehouse stamps a unique Barcode Sticker (surrogate key) on the box upon arrival. If the delivery address (natural key) changes, they update the address in the database under that barcode. The barcode sticker on the physical box never changes.

(5) Code Examples

Anti-Pattern: Natural Key Schema

CREATE TABLE authors (
  -- Email is the primary key
  email VARCHAR(100) PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
  title VARCHAR(100) PRIMARY KEY,
  -- Foreign key references a mutable email string!
  author_email VARCHAR(100) REFERENCES authors(email)
);

Best Practice: Surrogate Key Schema

CREATE TABLE authors (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- Surrogate Key
  email VARCHAR(100) UNIQUE NOT NULL,              -- Natural Key (with Unique)
  name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  -- Foreign key references a static integer ID
  author_id INT REFERENCES authors(id)
);

4. Common Mistakes & Pitfalls

Mistake 1: Believing a Natural Key like "Phone Number" or "Full Name" is a safe primary key

The mistake: Creating a table with phone_number VARCHAR(15) PRIMARY KEY because "everyone has a unique phone number."

Why it's wrong:

1. Phone numbers are recycled: If a user cancels their phone service, the provider eventually assigns that number to a new customer. If the new customer signs up, their record collides with the old user's history, causing data corruption.
2. Names are not unique: A system with 10,000 users will have multiple people named "John Smith," causing duplicate errors during signup.

Fix: Never use transient, recycled, or generic human details as primary keys. Default to artificial surrogate keys.

Mistake 2: Using Mutable Business Fields (like Email or SSN) as Primary Keys

The mistake: Defining email TEXT PRIMARY KEY on users table.

Why it's wrong: If a user updates their email, updating primary key values requires updating foreign key references across all child tables. Prefer immutable surrogate keys (IDENTITY, SERIAL, UUID).

Incorrect:

CREATE TABLE users ( email TEXT PRIMARY KEY ); -- ❌ Mutable natural key!

Fix:

CREATE TABLE users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email TEXT UNIQUE );

Mistake 3: Creating Un-Necessary Composite Natural Keys for Entities Lacking Natural Identity

The mistake: Creating a 4-column composite primary key on standard transactional tables.

Why it's wrong: Multi-column composite natural keys complicate foreign key references in child tables. Use simple surrogate keys (id).

Incorrect:

PRIMARY KEY (org_id, dept_id, year, seq_num) -- Complex foreign key propagation

Fix:

id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, UNIQUE (org_id, dept_id, year, seq_num)

5. Practice Exercises

Exercise 1: Surrogate Identity Primary Keys vs Natural Keys

Scenario: Compare surrogate primary key id INTEGER GENERATED ALWAYS AS IDENTITY against natural key email TEXT.

Requirements:

1. Contrast surrogate vs natural key trade-offs.

-- Recommended Surrogate Key Pattern
CREATE TABLE users (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

Exercise 2: Natural Key Usage in Lookup Tables

Scenario: Create a currency_codes lookup table using a natural 3-character ISO code (USD, EUR) as primary key.

Requirements:

1. Execute CREATE TABLE currency_codes (code CHAR(3) PRIMARY KEY, name TEXT NOT NULL).

CREATE TABLE currency_codes (
  code CHAR(3) PRIMARY KEY,
  currency_name TEXT NOT NULL
);

INSERT INTO currency_codes VALUES ('USD', 'US Dollar'), ('EUR', 'Euro');

Exercise 3: UUID Surrogate Keys for Distributed Systems

Scenario: Create a table using UUID as surrogate primary key (gen_random_uuid()) for client-side key generation.

Requirements:

1. Use id UUID PRIMARY KEY DEFAULT gen_random_uuid().

CREATE TABLE distributed_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_type TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

6. Related Terms

• PRIMARY KEY — The parent unique identifier.
• FOREIGN KEY — The constraint referencing the keys.
• UUID Type — Related concept: UUID Type.

7. Key Takeaways

• Natural keys use existing real-world values (email, ISBN); Surrogate keys use generated IDs.
• Natural keys are prone to changing, which can break cascaded table relationships.
• Surrogate keys are static, immutable, and faster to index and join.
• Modern standard: Use Surrogate Keys as PKs, and apply UNIQUE to Natural Keys.
• Never use recycled or volatile fields (like phone numbers or names) as keys.