Natural and Surrogate Keys

Natural versus surrogate identifiers, what InnoDB's clustered index charges for the choice, and what breaks when a natural key changes.

SyntaxPRIMARY KEY (surrogate_col), UNIQUE KEY uq_name (natural_col, ...)

Every table needs a primary key. The choice is whether that key is a value that already means something — an email address, an ISBN, an ISO country code, the pair (order_id, line_no) — or one the database invented, such as an AUTO_INCREMENT integer or a UUID.

InnoDB makes this a storage decision

The primary key is the clustered index: rows are stored in its order, and each record in a secondary index contains the primary key columns for the row, which InnoDB then uses to find the row itself. The manual draws the conclusion directly: "If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key."

So a CHAR(36) UUID key widens every secondary index on the table, and because its values are random it scatters inserts across the tree instead of appending. BINARY(16) written with UUID_TO_BIN(u, 1) halves the width, and swap_flag 1 moves the time-low part of a version 1 UUID so values sort roughly by time.

Declare something. Given no primary key, InnoDB uses the first UNIQUE index whose columns are all NOT NULL; failing that it builds a hidden index named GEN_CLUST_INDEX over a synthetic row ID you cannot query.

When a natural key changes

It eventually does. The value is copied into every child row that references it, so a change means ON UPDATE CASCADE rewriting an unknown number of rows, plus every cached copy outside the database. The usual resolution is to do both: a surrogate primary key for references, and a UNIQUE constraint on the natural key so it still cannot duplicate.

Example

Example · sql
CREATE TABLE customers (
  -- Surrogate: short, monotonic, meaningless, and therefore never needs to
  -- change. This is what every child table stores.
  id        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

  -- Natural: the value humans and other systems identify the row by.
  -- Constrained but not the primary key, so it can be edited with a single
  -- UPDATE that touches exactly one row.
  email     VARCHAR(255) NOT NULL,

  country   CHAR(2) NOT NULL,   -- ISO 3166-1 alpha-2: a genuinely stable natural key
  PRIMARY KEY (id),
  UNIQUE KEY uq_customers_email (email),
  CONSTRAINT fk_customers_country
    FOREIGN KEY (country) REFERENCES countries (code)
    ON UPDATE CASCADE          -- codes do change; DE and CS are the cautionary tales
) ENGINE=InnoDB;

CREATE TABLE invoices (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  -- References the surrogate. A customer changing their email is now one
  -- UPDATE of one row, not a rewrite of every invoice they ever received.
  customer_id BIGINT UNSIGNED NOT NULL,
  total_cents BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY idx_invoices_customer (customer_id),
  CONSTRAINT fk_invoices_customer
    FOREIGN KEY (customer_id) REFERENCES customers (id)
    ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB;

When to use it

  • Pick between AUTO_INCREMENT and UUID for a table whose identifiers appear in public URLs.
  • Decide whether a join table needs a surrogate id or should key on its two foreign keys.
  • Keep email unique without making every child table depend on it.
  • Explain why secondary indexes grew after someone changed the primary key to CHAR(36).

More examples

A UUID that does not punish the clustered index

The pattern for anything whose identifier appears in a URL or an API response, where a sequential integer would leak volume and invite enumeration. Two keys, each doing one job. UUID_TO_BIN(string_uuid, swap_flag) returns VARBINARY(16); with swap_flag 1 it swaps the time-low and time-high parts of a version 1 UUID so the rapidly varying part moves right, which keeps the index inserts clustered instead of random.

Example · sql
CREATE TABLE api_clients (
  -- Internal key: narrow and sequential, so it stays cheap inside every
  -- secondary index on this table and on every table referencing it.
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,

  -- External key: unguessable, safe to hand out, and 16 bytes rather than
  -- the 36 characters of the string form.
  public_id   BINARY(16) NOT NULL,

  -- Display form, computed on read. BIN_TO_UUID is deterministic, which is
  -- what a generated column requires. The swap flag MUST match the one
  -- used on the way in or the value comes back rearranged, not rejected.
  public_uuid VARCHAR(36)
    GENERATED ALWAYS AS (BIN_TO_UUID(public_id, 1)) VIRTUAL,

  name        VARCHAR(100) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_api_clients_public (public_id)
) ENGINE=InnoDB;

INSERT INTO api_clients (public_id, name)
VALUES (UUID_TO_BIN(UUID(), 1), 'billing-service');

-- Look one up from a value that arrived over HTTP.
SELECT id, name FROM api_clients
WHERE public_id = UUID_TO_BIN('6ccd780c-baba-1026-9564-5b8c656024db', 1);

When the natural key is the right primary key

Join tables are the clearest case for a composite natural key: both columns are already surrogate keys elsewhere, so they are stable, and their combination is exactly what the row asserts. The ordering decision is real — put the column you filter by most often first, because that is the order rows are physically stored in.

Example · sql
CREATE TABLE team_members (
  team_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  role    VARCHAR(20) NOT NULL,
  joined_at DATETIME NOT NULL,

  -- The pair IS the identity of the row. A surrogate id here would add a
  -- column, an index, and no meaning -- and you would still need a UNIQUE
  -- on (team_id, user_id) to stop duplicates, so the id earns nothing.
  --
  -- Column order matters: this is the clustered index, so rows for one
  -- team sit together and "members of team X" is a range scan.
  PRIMARY KEY (team_id, user_id),

  -- The reverse direction needs its own index; the clustered one cannot
  -- serve a lookup that does not start with team_id.
  KEY idx_team_members_user (user_id),

  CONSTRAINT fk_tm_team FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE,
  CONSTRAINT fk_tm_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB;

Migrating off a natural primary key

The migration you inherit rather than choose. The order is what makes it safe: the new column is nullable until the backfill is verified, the old foreign key stays until the new one is in place, and the natural key survives as a UNIQUE constraint so nothing about data quality is lost — only the dependency of every child row on a value that can change.

Example · sql
-- Symptom: orders.customer_email is the foreign key, so one address change
-- rewrites every historical order via ON UPDATE CASCADE.

-- 1. Add the surrogate and a nullable pointer on the child.
ALTER TABLE customers
  ADD COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (id),
  ADD UNIQUE KEY uq_customers_email (email);   -- the natural key still holds

ALTER TABLE orders
  ADD COLUMN customer_id BIGINT UNSIGNED NULL,
  ADD KEY idx_orders_customer (customer_id);

-- 2. Backfill through the old key while it still exists.
UPDATE orders o
JOIN customers c ON c.email = o.customer_email
SET o.customer_id = c.id;

-- 3. Only once nothing is NULL: enforce it and retire the old column.
ALTER TABLE orders
  MODIFY customer_id BIGINT UNSIGNED NOT NULL,
  ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
    REFERENCES customers (id) ON DELETE RESTRICT,
  DROP FOREIGN KEY fk_orders_email,
  DROP COLUMN customer_email;

Related

sql primary keysql uniquesql foreign keysql normalizationsql index intro

Discussion

  • Be the first to comment on this lesson.