Composite Keys vs Surrogate Keys: The Decision That Haunts Your Migrations

The Email Address That Was a Primary Key

Early on, it seemed obvious. Every user has a unique email, so users used email as its primary key. Orders referenced users by email. Order items referenced orders by (customer_email, order_no). No arbitrary IDs anywhere, and uniqueness was enforced by the keys themselves.

Then a user asked to change their email address. The UPDATE on users failed because orders referenced the old value. Someone added ON UPDATE CASCADE, and the change went through, rewriting the user row, 3,000 order rows, and 11,000 order item rows in one transaction while holding locks on all of them. Support tickets for email changes became an operational event.

A year later the company signed its first enterprise customer and needed every table to be scoped by tenant. Every primary key and every foreign key had to grow a tenant_id column. The migration plan ran to forty steps.

The primary key is the one decision in your schema that everything else copies. Foreign keys copy it, indexes copy it, API URLs and caches and other services copy it. Changing it later means changing all of those.

The Vocabulary

These aren't mutually exclusive. A common, solid design is a surrogate primary key plus a unique constraint on the natural key. Many arguments about "natural vs surrogate" go away once you realize you usually want both.

Why the Key Choice Spreads

A primary key doesn't stay in its own table. Here's where a customer's key ends up in a modest schema:

Three consequences follow from this:

1. If the key changes, every copy changes. Inside the database, ON UPDATE CASCADE can do this for you, at the cost of rewriting and locking every referencing row. Outside the database, in URLs, logs, caches, and other services' tables, nothing can.
2. If the key is wide, every copy is wide. A 30-byte composite key in a parent becomes 30 bytes in every child row and every index on those columns.
3. If the key's structure changes, every referencing table changes. Adding a column to a composite primary key means altering every foreign key that points at it.

The Case for Natural and Composite Keys

Natural keys have real advantages, and the strongest arguments for them are about correctness.

Uniqueness comes built in. If the primary key is (user_id, group_id) on a membership table, you physically can't add the same user to the same group twice. With a surrogate id and no other constraint, you can, and you will.

Fewer joins. If order_items carries customer_email as part of its key, you can find a customer's items without joining through orders. That's sometimes a real win for read-heavy paths.

The data explains itself. A row in country_currencies keyed by ('PK', 'PKR') is readable without looking anything up.

Composite keys can enforce relationships surrogates can't. This is the most underrated one. In a multi-tenant database, you want to guarantee that an order can only reference a customer in the same tenant. With surrogate keys alone, orders.customer_id can point at any customer in any tenant. With composite keys, it can't:

CREATE TABLE customers (
tenant_id  bigint NOT NULL,
id         bigint GENERATED ALWAYS AS IDENTITY,
PRIMARY KEY (tenant_id, id)
);

CREATE TABLE orders (
tenant_id    bigint NOT NULL,
id           bigint GENERATED ALWAYS AS IDENTITY,
customer_id  bigint NOT NULL,
PRIMARY KEY (tenant_id, id),
-- the order's tenant must match the customer's tenant
FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id)
);

That's a composite key built from surrogate parts, and it closes off a whole class of cross-tenant data leaks at the schema level. It also matches what sharded and distributed databases need: systems like Citus require the distribution column (here, tenant_id) to be part of every primary key and unique constraint, so rows for one tenant stay together on one node. (Database Sharding explains why.)

The Case Against Natural Keys

The problem with natural keys is that the real world doesn't keep its promises.

Natural keys change. People change their email addresses and usernames. Companies rebrand and change stock tickers. Books moved from 10-digit to 13-digit ISBNs in 2007. Products get re-numbered when a catalog is reorganized. Data that looks permanent today has a habit of becoming editable once the business grows.

"Unique" turns out not to be. Two customers share an email address because they're a family. A supplier reuses part numbers across product lines. An order number is unique per store, not globally, so the "natural key" was really (store_id, order_no) all along.

Natural keys leak information. If the key appears in URLs, an email-keyed resource puts personal data in access logs, browser history, and referrer headers.

Composite keys make every child wider and every migration bigger. Each level of a hierarchy inherits its parent's full key plus its own columns. By the third level, a foreign key might be four columns, and every JOIN has to name all of them correctly.

The Case for (and Against) Surrogate Keys

Surrogate keys solve the stability problem completely. An id never has a reason to change, so nothing that references it ever has to be rewritten. Foreign keys are one narrow column. Your ORM, your API, and your caches all get a simple, uniform identifier.

The costs are real but more manageable:

• Duplicates. A surrogate key guarantees each row is unique, not each thing. Without a UNIQUE constraint on the natural key, inserting the same customer twice succeeds, with two different IDs. This is the most common mistake in surrogate-key schemas.
• An extra index. That unique constraint is a second index you have to maintain. It's almost always worth it.
• Extra joins. Getting from an order item to the customer's email means joining through orders and customers instead of reading a column.
• IDs that say too much. A sequential integer reveals roughly how many rows you have, and makes neighboring IDs trivial to guess. That's not a vulnerability by itself, but it turns a missing authorization check into an enumerable one. (Insecure Direct Object References covers this.)

Integer or UUID?

If you need IDs generated outside the database (by clients, across services, before the row exists), UUIDv7 is now the sensible default. If the database always generates IDs and you're not exposing them, bigint is smaller and simpler. UUIDv4 is mainly worth it when you specifically don't want IDs to reveal ordering or time.

Watch a Key Change Ripple

The simulator uses a three-level schema (customers, orders, order items) with each key strategy. Change the strategy and the number of orders the customer has, and toggle the unique constraint on email.

customers   PK (email)
orders      PK (customer_email, order_no)
order_items PK (customer_email, order_no, line_no)

Things to notice:

• Composite natural keys with 5,000 orders. One email change rewrites over 20,000 rows across three tables. With any surrogate key, it rewrites one.
• Surrogate bigint, unique constraint off. Everything looks better, except that duplicate customers are now allowed. Turn the constraint on to fix it.
• UUIDv4 vs UUIDv7. Same size, but v4 lands new rows at random spots in the index while v7 appends. On a large table this is the difference between a hot, cached right edge and cache misses across the whole index.
• UUIDs vs the composite key on index size. A UUID is still smaller than a composite key built around an email address, but twice the size of a bigint, and in InnoDB that difference repeats in every secondary index.

The Migrations That Hurt

The cost of a key decision rarely shows up at design time. It shows up in these migrations:

Where Each Belongs

You don't have to pick one approach for the whole schema. Most well-designed schemas mix them:

Key Takeaways

• The primary key gets copied everywhere. Into foreign keys, indexes, URLs, caches, and other systems. Its stability and width affect all of them.
• Natural keys change more than you expect. Emails, usernames, product codes, and "unique" business numbers all turn out to be editable or duplicated eventually.
• Composite keys widen every child and every migration. Each level of a hierarchy inherits the full parent key, and changing the key's shape means rebuilding every reference.
• A surrogate key alone doesn't prevent duplicates. Pair it with a UNIQUE constraint on the natural key. This combination is the right default for most entity tables.
• Composite keys still have strong uses. Join tables, and (tenant_id, id) for multi-tenant data, where composite foreign keys stop references from crossing tenants.
• Pick the surrogate type deliberately. bigint for database-generated, internal IDs; UUIDv7 when IDs are generated elsewhere; never 32-bit int for a table that might grow.

References

• RFC 9562: Universally Unique IDentifiers (UUIDs), which defines UUIDv7
• PostgreSQL docs: UUID Functions, including uuidv7() (PostgreSQL 18+)
• MySQL docs: Clustered and Secondary Indexes, on how InnoDB secondary indexes store the primary key
• PostgreSQL docs: Foreign Keys, including composite foreign keys and ON UPDATE CASCADE
• Citus docs: Distributed table constraints, on including the distribution column in unique constraints