Keys: how a database knows one thing from another

The question a key answers

Two rows say Ade Okafor. Are they one person who ordered twice, or two people who share a common name? Nothing about the text can tell you. A key is the column, or set of columns, that settles it: the thing the database and the rest of your system agree means this one, and no other.

Natural keys and their disappointments

A natural key is a value that already exists in the world and looks unique: an email address, a phone number, a national ID, a vehicle registration.

Each one fails in a way you will meet:

• Email. People share them (family accounts), change them, and reuse them: an abandoned address gets reassigned by the provider a year later.
• Phone number. Recycled aggressively by telecoms. A number that was one customer’s is another’s within months.
• National ID. Not everybody has one, transcription errors are common, and storing it makes your database a target and a legal responsibility.
• A product’s SKU. Reissued by suppliers. Changed at rebrand.

The deeper problem is not uniqueness, it is stability. If the key changes, every row that referenced it must change too, and any exported report becomes unjoinable to the present.

Surrogate keys

So most tables use a surrogate key: a meaningless number the database generates and never changes.

CREATE TABLE customers (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email  text NOT NULL UNIQUE,
  phone  text
);

Both properties are present and they are different. PRIMARY KEY on id is the internal identity: permanent, meaningless, what other tables point at. UNIQUE on email is a business rule: two customers may not share an address today, and if that rule changes you drop the constraint without touching a single foreign key.

That separation is the whole design. Identity is internal and stable; business rules sit beside it and are allowed to change.

Sequential id or UUID? A bigint identity is small, sorts naturally, and produces compact indexes because new rows land at the end of the B-tree. A uuid can be generated on a phone that is offline, or by three services that never talk to each other, without collision: at the cost of 16 bytes instead of 8 and, for random UUIDs, writes scattered across the whole index, which measurably slows heavy insert workloads. Newer time-ordered UUID versions recover most of that locality. Pick a bigint unless you need to mint ids in several places.

Composite keys

Some tables have no single identifying column, and inventing one is worse than admitting it:

CREATE TABLE enrolments (
  student_id bigint REFERENCES students(id),
  course_id  bigint REFERENCES courses(id),
  enrolled_on date NOT NULL,
  PRIMARY KEY (student_id, course_id)
);

That primary key is a statement of fact: a student is enrolled in a course once. If they can re-enrol next term, the key is wrong and the real key includes the term. Get this decision right and a whole family of duplicate-row bugs never happens.

Foreign keys and what happens on delete

REFERENCES students(id) means the database checks, on every insert and update, that the student exists. It also decides what happens when the student is deleted, and this is a choice you must make rather than accept:

student_id bigint REFERENCES students(id) ON DELETE RESTRICT   -- refuse the delete
student_id bigint REFERENCES students(id) ON DELETE CASCADE    -- delete the payments too
student_id bigint REFERENCES students(id) ON DELETE SET NULL   -- orphan them deliberately

RESTRICT is the safe default for anything financial: you should not be able to make a year of payments vanish by tidying up a student record. CASCADE is right for rows that have no meaning alone: the items of a deleted draft order. SET NULL is right when the fact survives its owner, and it requires the column to be nullable, which every later query must then handle.

The single most common data-integrity failure in real systems is a foreign key that was never declared, “for performance”. You save a few microseconds per insert and buy an unknown number of rows pointing at customers who no longer exist, discovered years later by a join that quietly returns fewer rows than it should.

Where a key does not exist yet

Some data arrives with no reliable key at all: a spreadsheet of donations with names and dates and nothing else. You cannot conjure identity out of it. What you can do is be explicit: create a surrogate id for each row on import, keep the raw text, and treat “is this the same person as that?” as a separate, evidence-based step with its own lesson later in this course. The mistake is quietly assuming the name is a key, joining on it, and reporting the result.