MENU


Surrogate vs Natural Keys

Every table needs a primary key, and every primary key is either a natural key – an attribute that already exists in the real-world data and happens to be unique – or a surrogate key – a value generated purely to identify the row, with no business meaning of its own.

Natural keyDerived from the data itself: an email address, an ISO country code, an ISBN, a national ID number. Meaningful outside the database.
Surrogate keyGenerated by the database or application purely to identify the row: an AUTO_INCREMENT integer, a UUID, a Generated Invisible Primary Key (GIPK). Meaningless outside the database.

Natural Keys

For – no extra column, no join needed to know what the key means; the key is self-documenting and often already unique for a real business reason.
Against – real-world values can change (a customer's email, a company's legal name), and a primary key is meant to be immutable: every foreign key referencing it must cascade the change. Natural keys are also frequently wide or composite, which makes every foreign key column in every referencing table wider too, inflating index size and slowing joins. Some "natural" keys turn out not to be reliably unique in practice (national ID formats vary or get reused; the assumption breaks quietly, in production).

Surrogate Keys

For – stable and immutable by design (never carries business meaning, so it never needs to change when a business fact changes); narrow and fixed-width, which keeps the table's clustering index (InnoDB stores rows physically ordered by the primary key) and every foreign key referencing it compact and fast to join.
Against – meaningless to a human reading the data directly; does not by itself prevent duplicate real-world entities (a UNIQUE constraint on the natural key is still needed alongside it); a sequential AUTO_INCREMENT value leaks information (row count, creation order, easy enumeration of adjacent IDs in a public API).

AUTO_INCREMENT

The default choice: INT UNSIGNED or BIGINT UNSIGNED AUTO_INCREMENT. Compact (4 or 8 bytes), monotonically increasing (good for InnoDB's clustered index, which performs best with sequential inserts), simple to reason about. Downsides: predictable/enumerable, and awkward to generate client-side before an INSERT (the value isn't known until the row exists, which complicates offline/distributed ID generation and multi-master replication).

UUID

A 128-bit value, effectively guaranteed unique without coordinating with the database – useful when IDs must be generated client-side, offline, or across multiple independent systems before any row is inserted. MySQL provides UUID() to generate one, and UUID_TO_BIN() / BIN_TO_UUID() to store it as a compact BINARY(16) instead of a 36-character string. A plain random (v4) UUID as a primary key hurts InnoDB insert performance and causes index fragmentation, because new values land in random positions in the clustered index rather than at the end; UUID_TO_BIN(uuid, 1) reorders the time-based bytes to the front for a time-ordered (v1) UUID, restoring mostly-sequential insert order. Newer time-ordered UUID variants (v7) are designed to be sequential by construction and avoid this problem without needing the swap-flag trick.

GIPK – Generated Invisible Primary Key

Since MySQL 8.0.30, if a table is created with no explicit primary key and sql_generate_invisible_primary_key is ON, MySQL automatically adds one: an invisible BIGINT UNSIGNED AUTO_INCREMENT column named my_row_id. This exists mainly as a safety net – every InnoDB table benefits from having a primary key (InnoDB always clusters on one, generating an internal hidden key if none is declared), and row-based replication is far more efficient with an explicit key to identify rows – not as a recommended everyday way to get a primary key. An explicit, intentional primary key is still the better default; see Indexes for the full GIPK syntax, including how to reveal the generated column with ALTER TABLE ... ALTER COLUMN my_row_id SET VISIBLE;.

A Common Hybrid

Most production schemas use a surrogate key as the primary key for stability and join performance, while still declaring a UNIQUE constraint on the natural key to preserve real-world uniqueness and give the application a meaningful lookup column. See the worked example below. 

-- Natural key: ISO country code is stable, short, and genuinely unique
-- by international standard -- a reasonable case for using it directly
-- as the primary key rather than adding a surrogate.
CREATE TABLE countries (
    iso_code CHAR(2)     NOT NULL PRIMARY KEY,  -- e.g. 'US', 'SG'
    name     VARCHAR(80) NOT NULL
) ENGINE=InnoDB;

-- Surrogate key (AUTO_INCREMENT) as PRIMARY KEY, natural key (sku) kept
-- as a UNIQUE constraint -- the common hybrid approach.
CREATE TABLE products (
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sku        CHAR(10)     NOT NULL,           -- business/natural key
    name       VARCHAR(120) NOT NULL,
    UNIQUE KEY uq_products_sku (sku)
) ENGINE=InnoDB;

-- Foreign keys elsewhere reference the narrow surrogate, not the sku:
CREATE TABLE order_items (
    order_id   INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity   INT UNSIGNED NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id) REFERENCES products (product_id)
) ENGINE=InnoDB;

-- Surrogate key (UUID), stored compactly as BINARY(16). Useful when IDs
-- must be generated client-side, before any row exists in the database
-- (e.g. offline mobile clients, multi-service systems).
CREATE TABLE sessions (
    session_id BINARY(16) NOT NULL PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO sessions (session_id, user_id)
VALUES (UUID_TO_BIN(UUID(), 1), 42);   -- swap flag=1: time-ordered bytes first

SELECT BIN_TO_UUID(session_id, 1) AS session_id, user_id
FROM sessions
WHERE user_id = 42;

-- GIPK: table created with no explicit primary key.
SET sql_generate_invisible_primary_key = ON;

CREATE TABLE audit_log (
    event_name VARCHAR(80) NOT NULL,
    logged_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- MySQL silently added an invisible my_row_id BIGINT UNSIGNED AUTO_INCREMENT
-- primary key; SHOW CREATE TABLE audit_log; reveals it. Make it visible:
ALTER TABLE audit_log ALTER COLUMN my_row_id SET VISIBLE;