Keys & relationships
Tables on their own are just separate grids. KEYS are what wire them together into a real database. A primary key gives every row a unique name so you can point at it without confusion. A foreign key is a pointer — one table storing another table's id. Once you can point from table to table, you can build the three relationship shapes that describe almost every real situation: one-to-many, many-to-many, and one-to-one. Get these and you can model nearly anything.
Primary key: a row's unique name
Every table needs a way to point at exactly one row and no other. That's the PRIMARY KEY — usually a plain number column called 'id' that counts up: 1, 2, 3. Why a number and not, say, the name? Because names repeat — two people can both be 'Maya' — and a key that can repeat is useless for pinpointing one row. A primary key makes two promises: it's UNIQUE (no two rows ever share it) and it's never empty. Those promises are exactly what let '1' mean 'that one specific Maya, forever', even if she later changes her name or city. It's the row's permanent, unmistakable handle.
Foreign key: a pointer to another table
A FOREIGN KEY is how one table refers to a row in another table — by storing that row's primary key, not a copy of its data. Maya (id 1) places an order. The Orders table doesn't copy 'Maya, Pune' into the order; it just stores user_id = 1. That little number is the foreign key: it means 'this order belongs to the user whose primary key is 1'. The details still live once in Users; Orders just points at them. The database can also POLICE this pointer. Declare user_id a foreign key, and the database refuses to store an order pointing at a user that doesn't exist, and refuses to delete a user who still has orders. That guard rule — keeping every pointer honest — is called referential integrity. It's the database stopping you from creating broken links.
Composite key: when one column isn't enough
Sometimes no single column is unique on its own, but two columns TOGETHER are. Then the key is made of both — a COMPOSITE KEY. Picture a class register that records attendance: columns student_id and date. One student_id repeats (Maya is marked every day). One date repeats (lots of students share '5th June'). Neither column alone can name a single row. But the PAIR (student_id 1, date '5th June') points at exactly one attendance record — Maya, that day. So the primary key is the two columns combined. That's all a composite key is: a primary key built from more than one column because no single column is unique by itself.
The three relationship shapes
Once tables can point at each other, real relationships fall into three shapes: • ONE-TO-MANY (the most common). One user has many orders, but each order belongs to exactly one user. You build it by putting the foreign key on the 'many' side: every Orders row stores its one user_id. One Maya, many of her orders all pointing back at her. • MANY-TO-MANY. Each student takes many courses, AND each course has many students — both sides are 'many'. You can't store this with a single foreign key, so you add a third table in the middle, called a JOIN (or linking) table: an Enrollments table with one row per (student_id, course_id) pair. Each row says 'this student is in this course'. The many-to-many becomes two one-to-manys joined through the middle table. • ONE-TO-ONE (rare). Exactly one row on each side — one user has one passport, one passport belongs to one user. Usually you'd just keep it in the same table; you only split it out for special reasons (sensitive data, optional fields). Real, but uncommon.
It's a tradeoff
| Option | 👍 Pro | 👎 Con |
|---|---|---|
| A simple number id as the primary key | Always unique, never repeats, never changes, tiny to store, and fast for foreign keys to point at. The safe default for almost every table. | The number means nothing by itself — '1' tells you nothing about who it is. You have to look up the row to learn anything (which is exactly what relationships are for). |
| Using real data (like an email) as the key | The key is meaningful — you can read it and know who it is, no extra lookup. | Real data changes (people switch emails) and might not be truly unique forever. A changing key breaks every foreign key pointing at it, so this is risky. Most databases keep a stable number id and treat email as just another column. |
Questions you might have
▸What's the actual difference between a primary key and a foreign key?
A primary key lives in its OWN table and names each row there uniquely — it's the row's identity. A foreign key lives in ANOTHER table and is just a copy of some primary key, used as a pointer back. Same kind of value (usually an id number); different job. Primary = 'this is me'; foreign = 'I point at that one over there'.
▸Why do I need a separate table for many-to-many? Can't I just list course ids in the student row?
You could try to cram '3,7,9' into one cell, but that breaks the rule of one value per cell and makes it miserable to search ('which students take course 7?'). A linking table — one tidy row per (student, course) pair — keeps every cell single, makes both directions easy to query, and lets you add extras like a grade or enroll date to each pairing. It's the clean way every time.
▸How does the foreign key go on the 'many' side — why not the 'one' side?
Because the 'one' side would have to hold a list of all its 'many' (all of Maya's order ids), and a cell can't hold a list cleanly. The 'many' side, though, each points at just ONE thing — every order has exactly one user. So the single pointer fits neatly on the many side: one user_id per order. One pointer per row, no lists, no mess.
▸Can a table point at itself?
Yes — and it's surprisingly common. An Employees table can have a manager_id foreign key pointing at another row in the SAME Employees table (your manager is also an employee). A comment can have a parent_comment_id pointing at the comment it replies to. The foreign key just points at a primary key; nothing says it has to be a different table.
▸Is a composite key the same as having two foreign keys?
They often overlap but aren't the same idea. A composite key means the PRIMARY key (the row's unique name) is built from two columns together. In a linking table those two columns frequently happen to ALSO each be foreign keys (student_id points at Students, course_id at Courses) — so the row's identity and its two pointers line up. But composite-ness is about uniqueness; foreign-ness is about pointing. A column can be one, the other, or both.
Best read after: The relational model