Relationships & Keys (Primary & Foreign)

You've seen a database table — rows and columns, like a spreadsheet. Then someone tells you the real data is spread across five tables that all point at each other, and you have to "join" them back together to get a useful answer. It feels like someone took one clean sheet and shattered it on purpose.

They did, actually — and there's a good reason. Splitting data into linked tables is what keeps it from slowly rotting into a mess of contradictions. The links between those tables are made of two simple ideas: primary keys and foreign keys. Once you see what they are and why they exist, the whole "why is my data in pieces?" question dissolves, and JOINs — the thing that scares everyone next — turns out to be the easy part.

This guide assumes you already know what a table is. If "table," "row," and "column" are fuzzy, read What a Database Is first, then come back.

How to read this

• Want it to finally make sense? Read in order. Each phase builds the next: the problem (Phase 1), the anchor that names a row (Phase 2), and the link that connects rows across tables (Phase 3).
• Just need keys defined? Phase 2: Primary Keys and Phase 3: Foreign Keys & Referential Integrity stand on their own — but Phase 1 is what makes them feel inevitable instead of arbitrary.

The phases

1. Why Split Data Into Tables — the duplication problem: one big table repeats everything and rots. The fix is separate tables that reference each other.
2. Primary Keys — every row needs one stable, unique name the rest of the database can point at. What makes a good one, and why auto-numbers usually win.
3. Foreign Keys & Referential Integrity — a column that points at another table's primary key. How it models one-to-many and many-to-many, and how the database refuses to let your links break.

Deeper modeling territory — the formal normal forms, indexing strategy, and composite-key design — is deliberately left for later guides. Here we build the intuition that everything else stands on.