Foreign Key

There was a time when everything went into one table. Recording orders meant writing the buyer's name, email and company name onto every single order row, in full.

The pain arrives once the same fact is scattered across many rows. One customer renames itself and all 4,000 of its order rows have to be edited. Miss one and the company gets counted under two names, so any report that groups revenue by customer splits that customer into two pieces.

Splitting the data across tables became the standard answer. The company gets written once into a company table — a table being the row-and-column unit that actually stores records, one row per company — and each order row keeps only the company number. Now a rename is a one-row edit.

What the number alone doesn't give you is any guarantee that it points at a company that exists. At Pagedeck, Theo once deleted a test account. The account went away, but the documents it had created still carried the deleted member number, and the document list broke completely when it went looking for an author name and found nothing there. A foreign key is the lock you put on the database so a number with nothing behind it can't be stored in the first place.

Take the member number column on the orders table and register a rule with the database: any value here has to be a number that actually exists in the members table. That registration is what makes the column a foreign key, and from that moment the database checks the condition on every write.

Three pieces are needed.

1. A primary key on the side being pointed at — the column that uniquely identifies one row, such as the member number on the members table.
2. A column on the pointing side — the member number column on the orders table. Its data type has to match the primary key it references.
3. The constraint that joins them, declared when the table is created or added later through a migration, which is the work of changing the structure of a table that already holds data.

Once it's in place, two things get blocked. Saving an order with a member number that doesn't exist fails, and deleting a member who still has orders fails too. The delete side is configurable: cascade the delete, refuse it, or blank the value out. For some products deleting a member should take their documents with them; for others the documents stay and only the author column is cleared. A foreign key records that decision in the table definition rather than somewhere in the application code.

The check could live in the application instead, and the reason it doesn't is that there are many doors into a database. The web server writes, batch jobs write, and an operator occasionally edits rows straight from a console. Put the same check in every program and one of them eventually misses it, and the bad values that come in through that path only surface months later.

Foreign keys appear anywhere data has been split across multiple tables. That splitting is called normalization — carving tables up so the same fact is never written in two places — and a foreign key is what stitches the pieces back together.

The Pagedeck schema, the design document listing which tables exist and which columns each one has, carries several of them.

1. The author column on the documents table points at the members table. Showing an author name in the document list travels down that link.
2. The document column on the comments table points at the documents table, configured so that deleting a document deletes its comments too.
3. The team membership table points at both the members table and the teams table. Two foreign keys on one table is a common shape.

Reading follows those links to match rows across tables and return one combined result — asking for the document list with author names attached is exactly that.

There are also places where teams leave them off on purpose. Once data grows enough to be split across several servers, the pointing row and the row it points at can sit on different machines, which makes the check impossible. Tables under heavy write load sometimes drop it because the check costs too much. Whatever gets dropped becomes the application's responsibility, and the trade has the same shape as denormalization, where accuracy is given up a little in exchange for speed.

"There's a foreign key on that column, so deleting the member first throws an error."

Either change the order of the deletes or look at the cascade setting.

"That table just holds the number with no foreign key, so there are probably some broken rows in the data."

Nothing was checking, so values pointing at nothing have likely accumulated.

"This table takes a lot of writes, so we left the foreign key off and check it in the application."

Sharing a design decision to move the check outside the database.

When Theo sketches the tables for a new feature at Pagedeck, the first thing settled is which column points at which table. Building comments meant deciding, before the table existed, whether a comment points at a document or a member and what happens to comments when a document is deleted.

Deciding late is expensive. If rows pointing at nothing are already sitting in the data, registering the constraint is rejected outright and those rows have to be cleaned up by hand first. Pagedeck spent two days clearing roughly 300 broken rows while adding a foreign key to an early table six months after the fact.

People often assume a foreign key makes things faster. Linking tables and finding things quickly sit close together in most people's heads. The reality runs the other way: the constraint checks the other table on every write, so it makes writes slightly slower. Speed comes from an index, which works like the lookup pages at the back of a book. Many databases create an index automatically alongside a foreign key, which is why the two look like one thing.

The other frequent mistake is picturing the two tables as physically attached. The word "key" suggests something that opens a passage. What's stored is one number, and the contents of the other table are matched and fetched fresh on every read. Picture them as attached and you'll get surprising results on any screen that has to preserve the company name as it stood at the time of the order — last month's invoice comes out printed with this month's new name.