Primary Key

In a table that holds data as rows and columns, changing one particular row requires a way to point at it. Using something human-readable feels natural at first — find people by name or email address and a person can verify it by eye. The problem is that those values are neither unique nor fixed. Two people share a name, one person changes an address, and either case sends the same query at the wrong row.

Pagedeck's early user table was built that way. It had a name and an email address, and users were located by email. When one user's work address changed and they updated it, the 40 documents they had created ended up with no findable owner, because the document table held the old address. Theo spent half a day pairing old and new addresses by hand to repair it.

A primary key exists to keep that from happening, by setting aside one value that never changes. It doesn't need to be readable and shouldn't carry meaning — a value with no meaning has no reason to change. That column becomes the one thing in the table nobody edits.

When a table is created, one column is designated and two rules are attached to it: the value can't be empty, and the same value can't appear twice. The database enforces these itself, so a program that tries to insert a duplicate is refused on the spot.

There are two ways to produce the values.

1. An integer that increments by one automatically. Short, ordered, easy to read and cheap to store. The next value is guessable, though, and in an architecture where several servers generate values independently, the same number can come out twice. Exposed publicly, a sequential key also leaks roughly how many rows exist.
2. A long randomly generated identifier. The length costs storage and index space, but servers can generate values without coordinating and never collide, and the next value can't be guessed.

Either way, three more rules apply.

1. Don't use a value that carries meaning. Email addresses, tax IDs and employee numbers look unique today and can change. The moment one changes, everything pointing at it breaks. The failure is silent: nothing errors, the joins simply stop finding anything.
2. When one column isn't enough, several can be designated together. For a table recording which user bookmarked which document, the user value and the document value are combined into the key. That rule is what stops the same person bookmarking the same document twice. The database refuses the second insert rather than leaving that check to application code.
3. Only one per table. Other columns that must be unique get their own uniqueness constraint; they aren't the primary key. With two things pointing at a row, which one to join on varies from one piece of code to the next.

This column usually gets an index attached automatically, which is why looking up one row by key stays fast even with a million rows in the table. Without one, finding a single row means scanning the whole table.

It appears anywhere else that needs to point at this row.

The heaviest use is where tables join. A column pointing at another table's row is a foreign key, and the value it holds is that table's primary key. Normalization, splitting one table into several to cut duplication, makes every reconnection between the split tables run through this value. The key is what makes the split reversible.

It surfaces in screens and URLs. The value passed when someone clicks a row in a list to open a detail screen is this key, and so is the document number sitting in an API path. Put an auto-incrementing integer there as-is and changing one digit in the URL becomes an attempt to open someone else's document. That's why externally exposed positions use random identifiers, or separate the internal key from the value shown outside. Some teams keep both and translate between them at the edge of the system.

It shows up in records too. Writing a user's name or email into logs and analytics data breaks the link to past records when that value changes, and spreads personal information across systems. Keeping only the unchanging key leaves the option of attaching names later when needed. It's also the basis on which an analytics tool stitches one person's behavior together.

"Don't use email as the primary key. That's a value the user can change."

A unique value and an unchanging value are two different things.

"This table has no primary key. Three identical rows are already in it."

Without the constraint, duplicates accumulate quietly.

"If the number shows in the URL, someone can add one to it and open another person's document."

An internal identifier shouldn't be used outside as-is.

Reworking the user table, Theo added a meaningless identifier column and designated it the primary key. The email address moved down to an ordinary column with a uniqueness constraint of its own. The document table was changed to point at that identifier rather than at a name or an address.

After that, a changed email address broke nothing, and no part of the application code had to know that the address was no longer the identity. In the same pass, the sharing URLs that go outside were built from separate random values. Splitting the two also closed the path of reaching other people's documents by incrementing a document number.

The most common reading is that it's the same thing as the number shown to users, since both are numbers identifying one thing. But a number people read and say out loud, like an order number, invites a date or a branch code inside it, and that format eventually changes. Fold that requirement into the primary key and every table pointing at the value shakes on the day the format changes. A human-readable number is safer as its own column. Separate the two from the start and a format change stays a screen-side job.

Reading "unique today" as good enough is the other one, because the rule asks for uniqueness and a unique value seems to satisfy it. Two properties are needed, though: unique, and unchanging going forward. Uniqueness is a property of today's data; immutability is a promise about the future. A phone number is unique now and does change, and rows pointing at it can end up pointing at a different person with no warning at all.