Choosing a key: the decision that’s hardest to take back

Every row in a database needs a way to be pointed at. That’s the key. It looks like the most boring column in the table, and it’s the one decision I’d least like to get wrong, because everything else in the system ends up holding a copy of it.

Here’s what I learned about choosing one.

What a key has to be

Three properties, and a key that fails any one of them will eventually hurt:

1. Unique, for every row that will ever exist. Not just the rows I have today.
2. Always present. A row with no key value can’t be reached.
3. Never changing. Once a row has its key, it keeps it for life.

The third is the one people forget, and it does the most damage.

A table can have several columns that happen to be unique. A members table might have both a card number and an email. Each is a candidate key. The primary key is the one I choose to be each row’s identity, the one everything else refers to. There’s exactly one per table.

Why real-world data makes bad keys

A natural key is built from data that already exists: an email, a phone number, a licence plate. It’s tempting, because the data is already there and it means something. Then I test it:

• Email: families share one; not everyone has one; people change it constantly.
• Phone number: shared household lines, people switch numbers, and old numbers get reassigned to strangers.
• Full name plus date of birth: collides as soon as the table grows.
• Licence plate: changes when a car is re-registered, and personalised plates move between cars.

Almost every natural key fails at least one property, and it usually fails quietly, years after the design was chosen.

The failure that hurts most is change, because a key doesn’t live only in its own table. A loans table records who borrowed each book by storing that member’s key. If the key is the email and Layla changes hers, here’s the cost:

Miss one of those edits and that loan points to an email nobody has. Worse: if the old address is later given to someone else who joins, the missed loan now silently belongs to the wrong person. Nothing errors. It’s just wrong.

Generated keys, and the catch

A generated key is a value the system invents purely to identify the row. Member 4192 means nothing in the real world, and that's exactly why it works. It's unique, always present, and nothing in the world can ever make it wrong. Layla can change her email, her phone and her name, and she's still 4192.

The catch: a generated key gives every row a stable identity, but it doesn’t stop real-world duplicates. If Layla forgets she joined and signs up again, she becomes 5307, and the key is perfectly happy, because 4192 and 5307 are different numbers. A key only promises that no two rows share a key. It knows nothing about the person.

So I separate two jobs:

• Identity, which row this is forever, belongs to the generated key.
• Real-world rules, such as no two members sharing an email, belong to a uniqueness rule on that column. The database enforces it, but the email isn’t the key.

The separation pays off later. If the library decides families may share an email, I remove one uniqueness rule and nothing else changes, because nothing ever referred to members by email.

Get Ze Jang’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Natural keys are fine when someone controls the values and keeps them stable by design: country codes like US, currency codes like EUR, a fixed list of statuses my own system defines. The rule of thumb: if the real world can change it, reuse it, or fail to provide it, it shouldn't be the key.

When one column isn’t enough

A school records enrollments: which student takes which course, in which term. No single column identifies one. A student takes many courses; a course has many students; a student can retake a course in a later term. What identifies an enrollment is all three together. That’s a composite key: the combination must be unique, while each column on its own repeats freely.

The columns I pick are a statement about the real world: exactly which fact may exist at most once. Get them wrong and the rule is wrong:

• Too few columns (student and course) and the legitimate retake is refused.
• Too many columns (add the grade) and the same enrollment can be stored twice, once with an A and once with a C.

The right composite key is minimal: if I can remove a column and the combination is still unique, that column doesn’t belong.

Two lessons sit inside that. First, a composite key is only as stable as its parts, so I build it from stable ones, usually the generated keys of the things involved. Second, every value in a key must be controlled. If the term is typed as free text, Fall 2026, fall 2026 and Fall 26 are three different values, and the uniqueness rule leaks through every spelling. Terms exist on their own, with start and end dates, so they deserve a table of their own that enrollments refer to.

There’s a second design: give each enrollment its own generated number, and put a uniqueness rule on the combination. Which one? I ask whether anything else will ever point at one enrollment: a grade, an attendance record, a cancel button. If so, one generated number is far easier to carry around than three columns.

But there’s a classic mistake waiting. Someone adds the generated number and forgets the uniqueness rule. Every row now has its own number, the key is satisfied, and nothing stops the same enrollment being stored twice. Whichever design I pick, the combination’s uniqueness must be enforced somewhere.

And a table can have more than one such rule. Study-room bookings carry two: a room can be booked once per slot per day, and a member can book only one room per slot per day. That’s one primary key and one uniqueness rule, each enforcing a different fact about reality. Most designs that leak duplicates enforce the first rule and forget the second.

Where generated keys come from

There are two main families, and most of the arguments online are between them.

Counters. The database hands out 1, 2, 3 in order. They’re small and readable, and every new key lands at the end. But only one place can hand them out, so machines that create rows independently collide unless they coordinate. They leak volume: order 1043 today and 1243 next week means about 200 orders a week. And they invite guessing: a link ending in 1043 invites someone to try 1044. One sizing rule matters: a 32-bit number runs out at about 2.1 billion, and real systems have failed exactly there. Use the 64-bit kind.

UUIDs. 128-bit values like 550e8400-e29b-41d4-a716-446655440000, designed so anyone, anywhere can create one without asking anyone, with collision odds treated as zero.

• Version 4 is random. It reveals nothing and can’t be guessed.
• Version 7, standardised in 2024, starts with a timestamp followed by randomness. It can still be created anywhere, but it reveals roughly when the row was made.

The version is written inside the value itself: it’s the first character of the third group. One more rule: store UUIDs in the database’s native type, never as 36-character text.

The real difference between them is where each new key lands:

To find one key among millions quickly, the database keeps every key in a sorted structure, the index, and halves its search at every step, the way you’d find a word in a dictionary. That only works if every key sits in its sorted place, so every new key must be placed there. For counters and version 7, that place is the end, which is the cheapest spot possible. For version 4, it’s somewhere random in the middle, so every insert means making room inside a structure that’s already full. At scale, that’s real cost.

Choosing between them

• One database, keys used only inside the system: a 64-bit counter. That’s most applications.
• Keys created on many machines: UUID, preferably version 7.
• Keys shown to the outside world: something that can’t be counted or guessed. A clean middle path is to keep a counter as the internal key and give each row a separate random public identifier, the only one anyone outside ever sees.

And whatever I choose, hard-to-guess keys are not security. Permission checks are.

Underneath, every key scheme is a mix of four ingredients: a counter, a timestamp, a machine identifier, and randomness. Once I see which ingredients a scheme uses, I can predict both its strengths and what it leaks.

Why it’s worth getting right the first time

Changing a key later is possible, and expensive. Turning a 32-bit key into a 64-bit one means rewriting every row and every table that refers to it. Done carelessly on a big table, it blocks the table for hours, so it’s done in careful steps: add a new column, copy the old rows in small batches, write new rows to both, switch everything over, and finally drop the old column. Weeks of work, for a choice that would have cost 4 bytes per row on day one.

Moving from counters to UUIDs is harder still, because the old keys have already escaped: they’re in saved links, sent emails, other services. You can’t rewrite those, so the old numbers usually live on forever as a second way to look rows up.

That’s the real lesson of keys. Most design decisions are cheap to undo. This one isn’t, so it deserves the thinking up front, while it still costs nothing.