Primary Keys: Naming Every Row

Two customers named Anna Kim walk into the Marquee in the same month, and Lora's notebook quietly falls apart: a booking says "Anna Kim, seat G7" — which Anna? The data is all there, and it still can't answer. Databases solve this with a primary key: one column whose value uniquely names each row, forever. Give each Anna her own number, make bookings mention the number instead of the name, and the ambiguity is gone for good.

The primary key is the first rule the reader adds to a table — Chapter 1 called them constraints in passing, and this is the flagship. It looks like a small bookkeeping habit. It is actually the foundation the entire relational model stands on: the next two topics, and most of the rest of the book, hang off rows having reliable names.

Why Names and Emails Fail

The tempting move is to pick something the customer already has. Name? Two Anna Kims just demonstrated the problem — real-world attributes collide. Email, then — emails are unique, surely? Mostly, today. But emails change: Anna switches providers, and now every booking pointing at her old address points at nobody — or worse, at whoever registers that address next year. A key must be two things at once: unique (no two rows share it) and stable (it never changes for the life of the row). Real-world attributes keep failing one test or the other.

So databases mint an artificial one: customer_id, a number with no meaning outside the table. Customer 214 is customer 214 because the database said so on the day her row was created, and nothing about her life — name, email, address — can ever unsettle it. Think of membership card numbers: two Anna Kims hold two different card numbers, and the desk asks for the card, not the name. Mapped; from here on, the real term is primary key.

Declaring It

Here is the Marquee's third table, customers, with its key declared:

CREATE TABLE customers (
  customer_id  INTEGER PRIMARY KEY,
  name         TEXT,
  email        TEXT UNIQUE,
  joined_on    DATE
);

Two new words. PRIMARY KEY on customer_id tells the database: this column names the rows — every row must have a value here, and no two rows may share one. UNIQUE on email is a lighter promise: no two customers may register the same address, but email is not the row's name — it stays a changeable fact about Anna, merely one that can't be duplicated. Unique-but-not-the-key is exactly where emails belong.

And the database enforces the key the way it enforced types on the last page. Watch:

INSERT INTO customers (customer_id, name, email, joined_on)
VALUES (214, 'Anna Kim', 'anna.k@example.com', '2026-05-02');

INSERT INTO customers (customer_id, name, email, joined_on)
VALUES (214, 'Ben Ferro', 'ben.f@example.com', '2026-05-03');

The first statement stores the first row without complaint. The second tries to create another customer with customer_id 214 — and the database refuses it: 214 is taken, and a name shared by two rows names neither. The whole statement fails, nothing is stored, and the table keeps exactly one customer 214. This is "the database says no" again, now guarding identity instead of types.

Who Hands Out the Numbers?

Not you — and that is by design. In practice nobody types 214 into an INSERT; you leave the id out, and the engine mints the next free number itself. Ask for a new customer row, get back "that's customer 517," and move on. Automatic numbering removes the last human hands from the one column where a collision would be fatal.

One honest sentence on naming: the engines agree completely on the idea and not at all on the spelling — PostgreSQL calls it SERIAL (or the standard GENERATED … AS IDENTITY), SQLite calls it AUTOINCREMENT, and SQL Server calls it IDENTITY. Same feature, three names; when you meet any of them in a real schema, read them all as "the engine numbers this column."

What auto-numbering promises is only this: each new row gets a number no existing row has. It does not promise the numbers are consecutive — a refused INSERT can burn a number, leaving gaps — and you should never read chronology or importance into them. Which brings us to the last idea.

Keys Are Boring on Purpose

Customer 214 is not the 214th customer, not a VIP, not anything. The id carries no meaning — and that is its entire virtue. Meaningful things change: names get corrected, emails get replaced, addresses move. Meaningless things never need to. A key with zero content is a key with zero reasons to ever be updated, and "never changes" was half the job description.

So resist any urge toward clever keys — initials plus birth year, say, which collides and leaks personal data. The professional default is the boring one: <thing>_id INTEGER PRIMARY KEY, numbered by the engine. Every table in the Marquee schema follows it — film_id, screening_id, customer_id, booking_id — and nearly every schema you meet at work will too. Boring is not the beginner version; boring is the craft.