Primary keys

A primary key uniquely identifies each row and is the anchor for foreign keys, ORM identity, and often the clustered index order. It must be unique, non-null, and stable for the row's lifetime.

| Strategy | Pros | Cons | |----------|------|------| | Surrogate (`BIGSERIAL`, UUID) | Stable, simple joins | UUIDs widen indexes; sequential IDs leak volume | | Natural (email, SKU) | Human-meaningful | Can change; composite keys complicate ORMs | | Composite (`(tenant_id, local_id)`) | Fits multi-tenant sharding | Wider FK columns; ORM friction |

					-- PostgreSQL: time-ordered UUID v7 or bigint identity
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE
);
				

In InnoDB the primary key defines row clustering — random UUID inserts cause page splits; monotonic keys improve insert locality but expose ordering. Prefer `BIGINT` identity for high-volume OLTP unless you need global uniqueness without coordination.

On interviews: justify a key choice for a concrete table — why not email as PK, when UUID beats serial, and how migrations handle key changes.

Common pitfalls: no primary key (blocks replication tools, complicates ORM); mutable natural keys as PK; `INT` when tables will exceed 2B rows; random UUID PK on write-heavy InnoDB without measuring bloat.

The trade-off is balancing insert performance, global uniqueness, human readability, and migration safety — pick the key shape that matches how rows are created, referenced, and sharded.

Checklist:

• State why the PK will not change for a row's life.
• Compare surrogate vs natural for the domain.
• Mention clustering / index locality impact.
• Explain how child tables reference the PK.
• Note sharding or multi-tenant implications.