Primary and Foreign Keys

Primary Keys: Row Identity

 PRIMARY KEY = the guaranteed-unique row identifier:
 - NOT NULL, UNIQUE, exactly one per table
 - backed by an index automatically (clustered in some engines!)
 
 every design decision about keys cascades:
 index locality → insert speed → sharding → API exposure.

The Big Choice: Sequential vs Random

 AUTO-INCREMENT (serial/bigserial):
   1, 2, 3...   → inserts ALWAYS append rightmost leaf of B-tree
   ✓ fast appends, tiny keys, index-friendly
   ✗ leaks business volume via APIs (/trips/1043 → competitor counts)
   ✗ collision risk across shards/distributed generation
   ✗ merge pain between environments

 UUIDv4 (random):
   f47ac10b-58cc-...  → inserts land ANYWHERE in B-tree
   ✓ safe to generate anywhere (clients! shards!)
   ✓ non-enumerable public ids
   ✗ random inserts fragment B-trees; page splits everywhere
   ✗ 16 bytes vs 8

 UUIDv7 / ULID (time-ordered):
   timestamp-prefixed → mostly-appending like serial,
   globally unique like UUID — the modern default for new designs

Performance Impact, Concretely

 100M-row table, inserting at 10k/s:

 sequential ids:  writes hit RIGHTMOST pages (RAM-hot) → fast
 random UUIDv4:   touches across ENTIRE tree → cache misses,
                  page splits, WAL bloat → measurably slower
 
 rule: prefer ORDERED uniqueness (serial or v7/ULID)
 unless you need client-generated ids.

Natural vs Surrogate Keys

 NATURAL:    real-world attribute as key      (email, ISBN)
 SURROGATE:  meaningless id; natural value UNIQUE-constrained

 always-surrogate default, because natural keys rot:
   email changes → every FK referencing it must cascade-update
   surrogate id never changes; email is just a UNIQUE column.

 CREATE TABLE users (
   id    UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
   email TEXT NOT NULL UNIQUE          ← natural key demoted to constraint
 );

Foreign Keys: Enforced References

 FK = promise that referenced row EXISTS:

 trips.rider_id REFERENCES users(id)
   → cannot insert trip for ghost user
   → delete rules control orphaning (CASCADE/RESTRICT/SET NULL)

 costs worth knowing:
 - each FK check = lookup on parent's PK index (cheap, real)
 - hot write tables sometimes drop FKs for speed, enforcing
   in application + reconciliation jobs instead — a DELIBERATE trade

 indexes on FK columns are NOT automatic (in Postgres):
   every FK you query by needs its own index. audit this.

Interview Framing

“Choose identifiers for your entities” scored beats: surrogate-vs-natural reasoning (demote naturals to UNIQUE), sequential-vs-UUID tradeoffs with the B-tree locality explanation, UUIDv7/ULID as the resolution, FK indexing awareness. Public-API exposure of sequential ids (enumeration leak) is a favorite follow-up — flag it unprompted.