Natural vs Surrogate Keys

Concept

A natural key already exists in the real world and carries meaning: an email address, a national ID, an ISBN, a vehicle registration. A surrogate key is invented by the database purely to identify rows: an auto increment integer or a UUID.

Syntax

-- Surrogate key: MySQL and MariaDB
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY

-- PostgreSQL
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- SQL Server
id BIGINT IDENTITY(1,1) PRIMARY KEY

-- Natural key
CREATE TABLE countries (
    iso_code CHAR(2) PRIMARY KEY,     -- 'IN', 'UK', 'SG'
    name     VARCHAR(80) NOT NULL
);

Example: why stability matters

-- Natural key as primary key
CREATE TABLE customers_nk (
    email VARCHAR(120) PRIMARY KEY,
    name  VARCHAR(80) NOT NULL
);

CREATE TABLE orders_nk (
    id            INT PRIMARY KEY,
    customer_email VARCHAR(120) NOT NULL,
    CONSTRAINT fk_onk FOREIGN KEY (customer_email)
        REFERENCES customers_nk(email) ON UPDATE CASCADE
);

-- The customer changes their email address.
-- Without ON UPDATE CASCADE this fails; with it, every child row is rewritten.
UPDATE customers_nk SET email = 'new@example.com' WHERE email = 'old@example.com';

That single update rewrites every order row for that customer, plus every index entry containing the email, plus every row in any other table referencing it. With a surrogate key the same change touches exactly one column in one row.

The pattern most teams settle on

CREATE TABLE customers (
    id      BIGINT       NOT NULL AUTO_INCREMENT,   -- surrogate: the primary key
    email   VARCHAR(120) NOT NULL,                  -- natural: still enforced
    name    VARCHAR(80)  NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT uq_customers_email UNIQUE (email)
);

Surrogate key as primary, natural key as a UNIQUE constraint. You get stable references and cheap joins, and you keep the business rule the natural key represents. This is what the sample schema in this path does, and what this notes application itself does.

When a natural key is the right choice

• Small, genuinely immutable reference tables: ISO country codes, currency codes, two letter state codes.
• Junction tables, where the composite of two foreign keys is the natural key: assignments(employee_id, project_id).

Auto increment vs UUID

If you need UUIDs in MySQL/InnoDB, store them as BINARY(16) rather than CHAR(36), and prefer a time ordered form (UUID v7, or MySQL's UUID_TO_BIN(uuid, 1) swap flag) so inserts stay sequential in the clustered index.

Important rules

• A primary key should be unique, non NULL, narrow and immutable. Natural keys often fail the last one.
• Choosing a surrogate primary key never removes the need to enforce the natural key.
• In InnoDB the primary key is copied into every secondary index, so its width multiplies across the table.
• Random UUIDs as a clustered primary key cause page splits and index fragmentation at scale.

Common mistakes

• Using an email or phone number as the primary key of a person table.
• Adding a surrogate key and dropping the natural uniqueness rule.
• Adding a pointless id to a junction table whose composite key was already correct - it permits duplicate pairs unless you also add a UNIQUE.
• Storing UUIDs as CHAR(36) and tripling every index.
• Exposing sequential ids in public URLs when enumeration is a privacy concern.

Best practices

• Default to a narrow surrogate primary key plus UNIQUE constraints on the natural keys.
• Use natural keys for small, stable reference tables.
• Use the composite of foreign keys as the primary key of a junction table.
• If public exposure matters, keep the integer key internal and add a separate opaque public identifier.

Practice

1. Decide the primary key for: a countries table, a users table, an order_items table. Justify each.
2. Explain what breaks when a customer changes email in the natural key design above.
3. Why is BINARY(16) preferable to CHAR(36) for storing UUIDs in InnoDB?