Keys & Identity

FreshCart loaded its customers table from the mobile app and from the website checkout. Each source wrote a row per sign-up, and nothing in the table stopped 2 rows from describing the same person. Dorota Kaminski signed up on both.

The 2 rows differ by 1 capital letter and a channel. To the database they are 2 rows; to the business they are 1 customer with 6 orders in March totalling $412. The orders table stored the email she typed at checkout, so a revenue query joining on email matches each order against both rows.

Six orders became 12 and $412 became $824. The LOWER() that was meant to tolerate casing is what made both rows match, and the join multiplied every order row by the number of customer rows it found. Across 40 million customers, 1.8 million had a second row, and the March revenue report overstated the settled total by 11%.

Joins are the loudest symptom, and writes carry the same defect. An UPDATE customers SET marketing_opt_in = false WHERE email = 'priya.nair@example.com' changes 1 of her 2 rows, and the next campaign emails her anyway. A GDPR erasure DELETE removes 1 row and leaves the other for an auditor to find. A deduplication job keeping the newest row per name merges Dorota with every other Dorota Kaminski in the table.

The database can't invent the rule, but it enforces one once declared: a PRIMARY KEY rejects a repeated value, and a UNIQUE index on LOWER(email) would have rejected Dorota's second sign-up. Whether 2 rows with the same email are 1 customer is the modeller's question, because a household can share an inbox and 1 person can hold 2 accounts, and it gets answered before any constraint is written. The answer starts from the grain, what 1 row is, and the identity column is whatever makes 1 row at that grain distinguishable from every other.

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.

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.

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:

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.

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.

A foreign key is a column in one table whose values are drawn from the primary key of another. orders.customer_id holds values that exist in customers.customer_id, and the rule that says so is referential integrity: every value in the child column matches a parent row, or is NULL. In a one-to-many relationship the foreign key sits on the many side: each order has exactly 1 customer, so the customer's id fits in 1 column of the order row.

order_items carries 2 foreign keys because a line sits in 1 order and names 1 product. Read from the many side, the diagram is the business: lines belong to orders, orders to customers. A customers.order_id column would instead say each customer has 1 order.

PostgreSQL checks the constraint on every write, as do MySQL and SQL Server: an insert into orders naming a nonexistent customer fails, and deleting a customer with orders fails or cascades according to the declared action. The cloud warehouses behave differently. BigQuery and Snowflake accept FOREIGN KEY declarations, as do Redshift and Databricks, and none of them checks at write time, which is where data engineers get caught. The declaration is metadata the optimizer may use to skip a join it can prove loses no rows, and nothing more.

An orphan is a child row whose foreign key matches no parent. FreshCart's deduplication job merged 3,400 duplicate customers like Dorota's second row by deleting the losing row, and 41,000 genuine April orders kept pointing at the deleted ids. An inner join drops those orders, so revenue by segment undercounts by $2.4 million with no error. A left join keeps them with NULL in every customer column, and the $2.4 million lands in a segment the dashboard labels 'Unknown'. Either way finance and sales disagree until someone finds the gap weeks later.

CASCADE from orders to order_items is reasonable, because a line has no meaning without its order. CASCADE from customers to orders deletes revenue history: the deduplication job would have taken 41,000 real orders with it. A merge re-points children to the surviving row before deleting the loser. SET NULL keeps the orders and loses only the link, which is what a GDPR erasure usually wants: the money stays, the person goes. In a warehouse these actions are pipeline logic, and the dimension load checks for referencing facts before it deletes.

A composite key is a primary key made of 2 or more columns, unique in combination where no single column is unique on its own. The database treats the combination as 1 value for enforcement: (product_id, tag_id) can appear once, while product_id 40817 may appear in 30 rows and tag_id 7 in 20,000. The shape appears wherever the entity being identified is a relationship between 2 other entities rather than a thing of its own.

FreshCart tags products for search: organic, gluten-free, local. A product has many tags and a tag many products, a many-to-many relationship no foreign key on either side can hold: a product_id column on tags would allow 1 product per tag. The junction table product_tags resolves it with 1 row per product-tag pair and a foreign key to each parent.

The composite key (product_id, tag_id) states the business rule directly: a product carries a given tag once. The grain is 1 row per product-tag pair, and the key is the grain written down. A surrogate product_tag_id here would still need UNIQUE (product_id, tag_id), or the same pair could land twice; that constraint is the composite key under another name.

A junction table stays a bare pair until the business records something about the relationship itself, such as who applied a tag and when. The cost of a composite key shows once another table points at a row of product_tags, say a tag_reviews table of compliance checks on each assignment: its foreign key carries both product_id and tag_id, so every join on it is 2 columns wide, and so is the index behind it. At that width a surrogate starts to pay for itself.

The same pattern runs through dimensional modelling. A type 2 slowly changing dimension holds many versions of 1 customer, and the versions could be told apart by (customer_id, valid_from). The standard choice is a surrogate customer_sk per version, because 2.1 billion fact rows each store 1 key column instead of a natural key plus a date.

If product_tags also stored product_name, that column would depend on product_id alone. That's a partial dependency, a non-key column depending on part of the key, and second normal form forbids it: a rename reaches products while 30 rows in product_tags keep the old name. Every non-key column in a composite-key table depends on the whole key, and anything depending on part of it belongs in that part's own table.

A sequence is an atomic counter the database maintains: BIGSERIAL in PostgreSQL, IDENTITY in SQL Server and Snowflake. Each call returns the next integer, so keys arrive in insert order and pack B-tree indexes tightly, every new key landing at the right-hand edge. The key's width is the column's type, 8 bytes for BIGINT, and that's where tables go wrong: SERIAL and INT are 32-bit signed and stop a little past 2.1 billion. FreshCart created order_items with SERIAL in 2021, and at around 2 million lines a day it crossed that limit in its 3rd year. The next insert failed and took checkout down with it. The repair took ALTER SEQUENCE ... AS bigint plus an ALTER COLUMN ... TYPE BIGINT that rewrote the table under an exclusive lock for 2 hours.

A sequence's other limit is coordination: 1 counter in 1 database, so 400 Spark tasks writing a dimension can't each take the next value without serialising on it. BIGINT is the right default for anything that might pass 100 million rows; the extra 4 bytes a row come to 800 MB on 200 million rows, far cheaper than the outage FreshCart had.

A UUID is 128 bits. Version 4 is random, so any process anywhere mints one with no coordination, and with 2^122 possible values a collision isn't worth planning for. A native UUID column stores 16 bytes and the text form 36, so 500 million keys take 18 GB as text against 4 GB as BIGINT, before every foreign key and index copies them. Random values also scatter inserts across the index, fragmenting it. Version 7, standardised in RFC 9562, leads with a 48-bit millisecond timestamp, so keys sort by creation time and inserts cluster at the index's right edge, keeping the coordination-free property without the fragmentation. It's the default for ids minted on clients or across services.

A hash key is MD5 or SHA-256 of the natural key, with a separator so ('ab','c') and ('a','bc') stay apart: md5('FC-0417' || '|' || '2024-03-18'). The same input yields the same key on every run and every worker, so a fact load computes product_key from the natural key in parallel with the dimension load, with no lookup. Sequence keys exist only after the dimension insert, so every fact load waits for it and looks them up. Data Vault builds its hubs and links this way. The costs: MD5 is 16 bytes like a UUID, SHA-256 is 32, and hex text doubles each; the values have no order; and 2 inputs can in principle collide, negligible for SHA-256 and the reason some teams avoid MD5. The key is only as stable as its input: the recycled SKU hashes alike for bagels and oat milk unless the listing date is included.

A natural key still fits as the primary key where the value is short and set by a standard the business doesn't control, so it rarely changes: ISO country and currency codes, a calendar date_key written as 20240318. The test is unchanged: who can change or reuse the value, and how many rows would have to follow it.

Hash keys are worth their width where separate jobs must agree on a key without talking to each other; a single writer is still best served by a BIGINT sequence. The habits below are what the 5 sections add up to.