SQL / DEFINING TABLES AND CONSTRAINTS
Primary keys and choosing what identifies a row
Pick and declare the column or columns that identify a row, including composite and generated keys, and spot a candidate key that will fail later.
What you will learn
- Declare PRIMARY KEY (a, b) when only the two columns together identify a row
- Test each candidate key: unique in reality, known at insert, never edited
- Add a generated identity column when no natural candidate passes all three
- Keep UNIQUE on the natural key so a surrogate id cannot hide duplicate things
Understanding Primary keys and choosing what identifies a row
PRIMARY KEY (...) marks the columns you will use to point at one row and nothing else. The engine turns that into two mechanical guarantees, that the combination never repeats and never contains NULL, and usually builds an index behind it, but those are consequences rather than the reason the declaration exists. The reason is that later work needs a stable handle: an UPDATE that must hit exactly one row, a foreign key aimed at this table, an application that stores 'row 4172' and comes back tomorrow. A table can carry several unique columns; the primary key is the one you nominate as the handle everything else remembers.
Choosing it is a modelling decision, so put each candidate through three questions: is the value unique in the world rather than merely in today's rows, is it known at the moment the row is inserted, and will it never be edited? Full names fail the first, a purchase order number that only reaches you days after the order fails the second, and usernames or email addresses fail the third. A candidate has to pass all three, because one that fails any of them will eventually refuse a row that is true, or force you to rewrite the key. When nothing passes, let the database generate a meaningless id and keep the natural candidate as a UNIQUE column so the real-world rule is still enforced.
Identity is sometimes plural. A temperature reading is identified by which sensor and which instant, a ticket sale by which showing and which seat, and writing PRIMARY KEY (sensor_id, taken_at) says both that the pair is the handle and that one sensor has one reading per instant. Keep the key as narrow as the meaning allows: adding a column that identity does not need weakens the statement, because a wider key permits combinations the narrower one forbade.
-- PostgreSQL
CREATE TABLE reading (
sensor_id text,
taken_at timestamp,
celsius numeric(4,1),
PRIMARY KEY (sensor_id, taken_at)
);
INSERT INTO reading VALUES
('roof', '2026-03-01 08:00', 4.5),
('roof', '2026-03-01 09:00', 6.1),
('cellar', '2026-03-01 08:00', 11.2);
-- one sensor cannot hold two temperatures at one instant
INSERT INTO reading VALUES ('roof', '2026-03-01 08:00', 4.6);A primary key is a promise that one column or set of columns identifies a row uniquely, is known when the row is inserted, and never changes, so choosing it is a claim about the world and not just a duplicate check.
Worked examples
A generated key that outlives the details
Shows a surrogate primary key staying fixed while the attribute a beginner would have used as the key changes.
-- PostgreSQL
CREATE TABLE member (
member_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
joined_on date NOT NULL
);
INSERT INTO member (email, joined_on) VALUES
('ada@example.org', '2026-01-04'),
('grace@example.org', '2026-02-11');
UPDATE member SET email = 'ada.lovelace@example.org' WHERE member_id = 1;
SELECT member_id FROM member WHERE email = 'ada.lovelace@example.org';Example explained
Line 1GENERATED ALWAYS AS IDENTITY makes the database supply member_id, so the row has an identity before anything about the person is known or verified.
Line 2email still carries NOT NULL UNIQUE, because a generated key says nothing about the world and the rule 'one account per address' has to be stated separately.
Line 3The UPDATE rewrites the email but not member_id, which is why the row can be corrected without disturbing anything that remembered it.
Line 4The SELECT returns 1 rather than a new value: the handle survived the edit.
A natural key that turns out not to be unique
Shows a syntactically perfect primary key rejecting a row that is true, because the chosen column does not identify anything.
-- PostgreSQL
CREATE TABLE patient (
full_name text PRIMARY KEY,
born_on date NOT NULL
);
INSERT INTO patient VALUES ('Maria Silva', '1981-07-02');
-- a different person who happens to share the name
INSERT INTO patient VALUES ('Maria Silva', '1994-11-19');Example explained
Line 1A text column is a legal primary key, so the table is created and the first patient is stored without complaint.
Line 2The second INSERT describes a real second patient, and the key choice is what refuses it.
Line 3Nothing here is a bug in the constraint: the promise 'a name identifies a patient' was simply false about people.
Line 4The repair is a key change, not a data change, giving patient a generated patient_id and demoting full_name to an ordinary column.
Important notes
Standard SQL makes primary key columns NOT NULL and PostgreSQL, MySQL and SQL Server enforce it, but SQLite's ordinary rowid tables accept NULL in a non-INTEGER primary key column, so add NOT NULL yourself (or use WITHOUT ROWID) when working there.
A table gets exactly one primary key; a second PRIMARY KEY clause is rejected outright, and any other genuinely unique column belongs in a UNIQUE constraint instead.
Common mistakes
Using a name as the key because today's rows happen to be distinct: the first genuine namesake or repeated product title is rejected, and the usual patch is invented data like 'Maria Silva (2)'.
Keying on a value users edit, such as username, email or phone: correcting a typo becomes an UPDATE of the identity itself, and every copy of the old value in other tables, exports and bookmarked URLs now points at something that no longer exists.
Adding an id column to every table and stopping there: since the id is unique by construction, the table happily stores the same invoice or the same signup twice, each with its own id.
Try it yourself
Change, predict, then run
Create a seat_sale table where one row means one seat sold for one showing, with showing_id, seat_row, seat_number and sold_at, and declare the primary key you think identifies that row. Then insert the same seat for the same showing twice and check which statement the database refuses.
Open the SQL workspaceCheck your understanding
An employee table has an employee_email column that is never null and has no duplicates in the current data. Why do experienced designers usually still add a generated employee_id as the primary key?
- Text columns cannot be primary keys, so the email is not eligible
- A primary key must be a single integer column, and an email is not one
- Email addresses get corrected and replaced, and anything that stored the old key value would point at a value that no longer exists
- Keying on employee_id removes the need for a UNIQUE constraint on the email
Show answer
Primary key values are copied outward into other tables, exports, URLs and caches, so a key that can change forces all those copies to change with it; a generated id has no reason ever to change. Ruling the email out for being text is wrong, since stable text keys like 'EUR' or 'PT' are fine, and dropping UNIQUE on the email is worse than useless: the generated id makes every row distinct, so without that constraint the same employee can be stored twice.