🔑PRIMARY KEY

The table’s canonical row identifier

A PRIMARY KEY combines UNIQUE and NOT NULL: exactly one per table in practice, and it is the usual target of FOREIGN KEY references. Storage engines often cluster rows by the PK (SQL Server clustered index, InnoDB) or use it as the default B-tree, so key width, monotonicity, and update frequency affect physical layout, not only logical uniqueness.

SQL

Surrogate key with BIGSERIAL (PostgreSQL)

CREATE TABLE core.reagents (
  reagent_id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

✓Illustrative result set

status
CREATE TABLE

⚖️Surrogate vs natural keys

Most OLTP schemas use a narrow surrogate PK plus UNIQUE on business identifiers.

Style	Pros	Cons
BIGINT / identity	Compact joins, friendly to B-tree range scans	Monotonic IDs can leak volume; hot tail inserts on single pages
UUID (random)	Global uniqueness without a central allocator	Wider keys; random insert patterns fragment clustered stores
Natural only (email)	Human-meaningful joins in ad hoc SQL	Humans and mergers change identifiers, surprise key updates

Composite PK with five columns

Every child FK copies the whole wide key into its index, join and backup costs grow superlinearly.

Narrow surrogate + UNIQUE on the composite business key

Prefer BIGINT surrogate as PK; enforce natural uniqueness with UNIQUE constraints sized to what you actually filter on.

API exposure

Public identifiers at the edge (opaque tokens) can differ from internal PKs, document mapping and rotation when compliance requires erasure of external ids.

Updates

Changing PK values cascades to children, design keys as immutable; if business rekeys, add a new column and migrate with dual-write phases.

⚡ Advanced / dialect note

Standard SQL also allows no explicit PK, relational theory only needs candidate keys, but production tables without some uniqueness enforcement are where duplicate-row incidents begin.

Sharding

If you shard by tenant, co-locate tenant id in the PK or clustering key so related rows stay partition-local; random UUID PKs alone give you no locality story.

TL;DR

A primary key uniquely identifies each row and is indexed (often clustered on SQL Server). It enforces NOT NULL + uniqueness and is the parent target of foreign keys. Prefer stable surrogate keys for joins plus separate UNIQUE constraints on natural business identifiers when integrations need them.