Chapter 02 · basic · 6 min
Primary keys and uniqueness
Nothing in a plain table stops two rows from being identical, or from both claiming to be "customer 1". A primary key is a constraint that makes that impossible for one column (or set of columns) — and Postgres enforces it the same way it enforced price's type in the last chapter: by refusing the row outright.
We'll use a customers table with a primary key and a unique email.
The dataset
One table, customers (id PRIMARY KEY, name NOT NULL, email UNIQUE), seeded with two customers.
Schema
| id | INT |
| name | TEXT |
| TEXT |
Example data
A primary key means "this row, uniquely"
id INT PRIMARY KEY declares that no two rows in customers may ever share an id. It's the column you'd hand someone if they needed to point at exactly one row and no other.
Before you run it — how many rows, and which column would you use to uniquely identify one of them?
Postgres refuses a duplicate key
Try to insert a third customer with id = 1, already taken by Aisyah.
Before you run it — id 1 already belongs to Aisyah. Does this insert succeed and overwrite her, or does something stop it?
UNIQUE is the same idea on a non-key column
email is declared UNIQUE, not a primary key — but the enforcement is identical: no two rows may share the same value.
Before you run it — id 3 is new, but the email is Aisyah's. Does the new id save it, or does the duplicate email still block it?
UNIQUE tolerates NULL; duplicates, it doesn't
What if a customer just has no email yet? Two customers can both have a NULL email — Postgres treats NULL as "unknown", and two unknowns are never considered equal to each other, so UNIQUE doesn't block them.
Before you run it — this omits email entirely (NULL). Given the last two chapters, do you expect this to succeed or fail?
Two missing emails in the same statement
One INSERT can carry several rows at once — still one statement, just multiple VALUES tuples. Here both new customers omit email.
Before you run it — both new rows have no email. Does the second NULL collide with the first the way Aisyah's real email would have?
PRIMARY KEY can never be NULL, even though UNIQUE alone can
A primary key is UNIQUE plus NOT NULL — Postgres adds the not-null rule automatically. Try inserting a customer with no id at all.
Before you run it — `id` has no DEFAULT, so it would be NULL. Does that succeed the way a missing email did?
If you've used Excel or Google Sheets
A spreadsheet's row number looks like a primary key, but it isn't one — insert a row above row 5 and everything renumbers, and nothing stops two literal customer_id values in a data column from being identical, which is exactly the bug that makes a VLOOKUP silently return the wrong row's data. A real PRIMARY KEY constraint is what a spreadsheet is missing: an enforced guarantee, not a convention you have to maintain by hand.
A primary key guarantees uniqueness within one table. Next: what happens when a value in one table is supposed to point at a row in another — and what stops that pointer from going nowhere.