Primary Keys: Data Identity
Concepts covered: Primary Keys, Surrogate Keys
A primary key is the column, or set of columns, the schema guarantees is unique and non-null for every row. The database enforces it with an index: an insert that repeats an existing key value fails before the row lands. Everything else leans on that guarantee: a join matches on it, a foreign key points at it, an UPDATE ... WHERE id = ? touches 1 row because of it, and a MERGE uses it to choose between update and insert.
The 4 properties
Uniqueness and non-null are what the constraint enforces. Immutability and minimality are the modeller's choice, and so is width, since every foreign key and index entry copies the key: the database accepts a 120-character email as a primary key, and an UPDATE to it. A key that never collides stays unique across 3 boundaries: the rows in the table now, the rows it will hold over the years, and rows merged in from another system.
Collision across time: the recycled SKU
FreshCart's products table used sku as its primary key. SKU FC-0417, a 6-pack of sesame bagels, was discontinued in January 2023, and merchandising reuses codes: in March 2024 FC-0417 came back on a 1-litre oat milk. order_items referenced products by sku, so every bagel order since 2021 now joins to oat milk, and the bagels have no product row at all. The business needed both rows:
| sku | product_name | listed_from | discontinued |
|---|---|---|---|
| FC-0417 | Sesame Bagels 6pk | 2021-04-12 | 2023-01-31 |
| FC-0417 | Oat Milk 1L | 2024-03-18 | NULL |
- 21 months
The table can't hold both rows, because the key says they are 1 product. sku was unique in 2021 and is unique today; it collided across time because the system minting it has a reuse policy the warehouse doesn't control. Emails fail the same way: customers change them, and some providers hand released addresses to new owners.
Natural keys and surrogate keys
A natural key is a value from the business domain that identifies the entity: an email, a SKU, an ISO country code, a VIN. A surrogate key is a value the system generates purely to identify the row: a sequence integer, a UUID, a hash. Nothing in the business can edit or recycle it, which is where its immutability comes from. The natural key stays as an attribute, so a lookup by SKU still works, but other tables stop pointing at it.
- Natural key as PK
- Readable in every join and every log line
- Changes when the business changes it, and the change must reach every referencing row
- Reused by the source system without warning
- As wide as the email or name it holds, often 20 to 120 characters
- Surrogate key as PK
- Meaningless integer or UUID, so nothing in the business can alter it
- Fixed at 8 or 16 bytes and copied into every foreign key
- Holds 2 rows for 1 natural key, such as both listings of FC-0417
- Needs the natural key kept UNIQUE at its true grain, or duplicates get in
The decision turns on who controls the value. A countries table keyed on the ISO 3166 alpha-2 code is sound: the standards body changes a code about once a decade and the table is tiny. A products table keyed on a SKU that merchandising recycles is a collision waiting for a reorder. FreshCart moved to a product_id BIGINT surrogate: the bagels became 40817 and the oat milk 118204, each carrying FC-0417. Uniqueness moved to the grain the SKU is actually unique at, UNIQUE (sku, listed_from), so each listing period appears once and a replayed product load can't insert the oat milk twice.
FreshCart is adding a stores table for its 312 pickup locations. Each store has a store_code like 'LDN-014' assigned by operations, unchanged in 9 years. Which primary key is defensible?
- store_code as the primary key, since it has been stable for 9 years.
- A store_id BIGINT surrogate, with store_code as a UNIQUE attribute.
- store_name as the primary key, since the business refers to stores by name.
- No primary key, since 312 rows are too few to matter.
A surrogate gives the row an identity the business can't disturb. What other tables do with that identity, and what the database does when the row behind it disappears, is the subject of foreign keys.
About this lesson
This section is part of the Keys & Identity lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.