Primary Keys: Identity for Every Row

The row's permanent identity

A primary key is a row's permanent name — unique across the table, never null, ideally never changing. Without one, you cannot reliably point at a row from another table, from your application, or from a future you debugging at 2am. Every table should have one. No exceptions.

What makes a good primary key

Unique. No two rows share the value.
Not null. Every row has one.
Immutable. The value never changes after the row is created.
Compact and indexable. Integer or UUID — not a 200-character composite of business fields.

Surrogate vs natural keys

A natural key uses real-world data (email, ISBN, phone). A surrogate key is a system-generated value with no business meaning (auto-increment integer, UUID). Prefer surrogate keys; keep natural fields like email as UNIQUE constraints. Real-world identifiers do change (people change emails, ISBNs get reissued), and changing a primary key cascades pain through every foreign-key reference.

Composite primary keys

For pure junction tables (many-to-many bridges), a composite key of the two foreign keys is the natural fit and saves you a redundant surrogate column.

Code

Surrogate primary key (recommended default)·sql

CREATE TABLE users (
    id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    name  TEXT NOT NULL
);

Composite primary key for a junction table·sql

CREATE TABLE course_enrollments (
    student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
    course_id  INTEGER NOT NULL REFERENCES courses(id)  ON DELETE CASCADE,
    enrolled   DATE NOT NULL DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id)
);

UUID primary key for public-facing IDs·sql

CREATE TABLE api_tokens (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Primary Keys: Identity for Every Row

The row's permanent identity

What makes a good primary key

Surrogate vs natural keys

Composite primary keys

Code

External links

Exercise

Progress

Comments 0