Primary keys, and why not to use a business value

A primary key identifies a row. Every table should have one, and the choice of what to use is one of the few decisions in this course that is genuinely difficult to undo.

What it does

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

PRIMARY KEY is three things at once:

NOT NULL — a row must have one.
UNIQUE — no two rows share one.
An index — PostgreSQL creates one automatically, so lookups by id are fast.

It is also what foreign keys point at, which is why the choice propagates.

Never use a business value

The rule, and it is close to absolute: do not make a primary key out of something that means anything.

CREATE TABLE members (email text PRIMARY KEY, name text NOT NULL);       -- no
CREATE TABLE books   (isbn  text PRIMARY KEY, title text NOT NULL);      -- no
CREATE TABLE members (phone text PRIMARY KEY, ...);                      -- no

Every one of those looks reasonable and every one has ended badly for somebody.

Business values change. A member changes their email. Now every loan referencing that email must change too, in the same transaction, or the data breaks. A meaningless id never changes, so nothing referencing it ever has to.

Business values are not as unique as you think. The examples are endless: two books genuinely share an ISBN because a publisher reused it; two people share a phone number because it is a family landline; a person has two email addresses and you have created two members.

Business values leak. /books/978-81-7234-567-2 tells the world your ISBN; /members/asha@example.com puts an email address in logs, browser history and referrer headers.

Business values are big. An email is 30-odd bytes and every foreign key copies it. A bigint is 8. On a table with millions of rows and three referencing tables, that difference is real.

And the rule follows: if it can change, it is not an identity. It is an attribute — important, probably UNIQUE, and not the key.

CREATE TABLE members (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text UNIQUE,              -- unique, and not the key
  ...
);

You keep the guarantee that no two members share an email, and you keep the ability to change one.

`bigint` or `uuid`

The real choice.

`bigint` identity

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

For: 8 bytes; sequential, so inserts go to the end of the index and it stays compact; readable in a URL and over the phone; the default for good reasons.

Against: guessable — /orders/1247 invites somebody to try /orders/1248, so it is never a substitute for an authorisation check; it leaks volume, because a competitor ordering from you twice a week can estimate your order rate; and it needs coordination, so two databases cannot both generate ids without colliding.

Use bigint, not integer. integer runs out at about 2.1 billion, which sounds enormous until it is a high-volume table and the fix requires rewriting it. The four extra bytes are the cheapest insurance in this course.

`uuid`

id uuid PRIMARY KEY DEFAULT gen_random_uuid()

For: generatable anywhere — the client, another service, an offline app — with no coordination; not guessable; leaks nothing about volume; and it lets you know the id before you insert, which simplifies some code a great deal.

Against: 16 bytes rather than 8, in every index and every foreign key; random, so inserts land all over the index, which fragments it and hurts write performance and cache behaviour at scale; and unreadable — nobody reads one over the phone.

UUIDv7 fixes the worst of it. It is time-ordered, so it inserts sequentially like a bigint while keeping the other advantages. PostgreSQL 18 has uuidv7() built in; before that it is an extension or generated in the application.

Choosing

Internal ids, one database, sequential is fine   →  bigint identity
Ids generated by clients or several services     →  uuid (v7 if you can)
Ids exposed publicly where guessing matters      →  uuid, or a separate public token

The middle way worth knowing: a bigint primary key for internal use and a separate uuid or random slug column for external URLs. You get compact internal joins and unguessable public identifiers, at the cost of one extra column and index.

Composite keys

A key of more than one column:

CREATE TABLE book_categories (
  book_id     bigint NOT NULL REFERENCES books(id)      ON DELETE CASCADE,
  category_id bigint NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  PRIMARY KEY (book_id, category_id)
);

For a pure join table this is correct and preferable. The pair is the identity — a book is in a category or it is not — and the constraint prevents the same pairing being recorded twice, which a separate id column would not.

Order matters for the index it creates: (book_id, category_id) supports lookups by book_id alone but not by category_id alone. If you query both ways, add a second index.

Do not use composite keys for ordinary tables. Every referencing table then needs all the columns, joins get longer, and application code and ORMs handle them badly.

And if the join table acquires attributes of its own — a date, a note, a status — it has become an entity and probably wants its own id.

The table with no primary key

Legal in PostgreSQL, and a mistake.

Without one you cannot reliably update or delete a single row — DELETE FROM t WHERE name = 'Asha' may remove three. Duplicates accumulate silently. Nothing can reference the table. Replication tools frequently require one. And ORMs will not work with it.

If a table genuinely has no natural identity, add a surrogate one. It costs 8 bytes.

Natural keys, where they are fine

Being fair to the other side, because the rule has exceptions.

Small, fixed lookup tables where the code is the meaning and genuinely never changes:

CREATE TABLE currencies (code char(3) PRIMARY KEY, name text NOT NULL);   -- 'INR'
CREATE TABLE countries  (code char(2) PRIMARY KEY, name text NOT NULL);   -- 'IN'

INR will not be reassigned to a different currency. The value is short, stable and meaningful in a URL, and the join reads better.

Even here, be careful: country codes have changed, and currency codes have been retired and reused. If in doubt, use a surrogate.

Check your work

Three things PRIMARY KEY gives you. NOT NULL, UNIQUE, and an index.

The rule. Never a business value.

Four reasons. They change; they are not as unique as you think; they leak into URLs and logs; and they are large in every foreign key.

The principle behind it. If it can change, it is not an identity — it is an attribute.

What to do with the email instead. Keep it, UNIQUE, as a column.

bigint versus integer. integer runs out at 2.1 billion; the four extra bytes are the cheapest insurance here.

What a sequential id leaks. Volume — and it is guessable, so it is never an authorisation check.

The two costs of uuid. 16 bytes everywhere, and random inserts fragment the index.

What UUIDv7 fixes. It is time-ordered, so it inserts sequentially.

The middle way. A bigint internally and a uuid or slug for public URLs.

When a composite key is right. A pure join table, where the pair is the identity.

When it stops being right. When the join table acquires attributes of its own.

What you lose with no primary key. Reliable single-row updates, referencing, replication and ORM support.

Practice

Create a members table with email as the primary key. Insert a member, give them a loan, then change the email. Note everything that has to change with it.
Rebuild it with a surrogate id and email UNIQUE. Change the email again.
Find two real-world cases where a "unique" business value turned out not to be.
Create a table with an integer primary key and work out at what row count it fails.
Create a table with uuid PRIMARY KEY DEFAULT gen_random_uuid(). Insert five rows and look at the ids.
Compare the on-disk size of a bigint and a uuid index over 100,000 rows using pg_relation_size.
Read about UUIDv7 and say in one sentence what it fixes.
Design the book_categories join table with a composite key. Then try to insert the same pair twice.
Add an added_on column to it and argue whether it should now have its own id.
Create a table with no primary key, insert three identical rows, and try to delete just one.
Write down the primary key of every table in a project of yours and mark any that are business values.
For a currencies table, argue both sides of using char(3) as the key.

Official documentation

PostgreSQL — Primary keys — What the constraint does, including composite keys.
PostgreSQL — UUID type — Storage size and the generation functions.
PostgreSQL — Numeric types — The actual ranges of integer and bigint.
RFC 9562 — UUID formats — The specification that defines UUIDv7 and why it is time-ordered.

Next: the rest of the constraints.