Database Relationships and Foreign Keys

You could cram every order, with the buyer's name and city and the product's price, into one giant table. People try it. Then Maya changes her city, and now you're hunting through forty order rows to fix the same name in all of them, and you'll miss one. The fix is to stop repeating yourself: keep each fact in exactly one place and point to it. That pointer is a foreign key, and it's what makes our three little tables a database instead of three lonely spreadsheets.

Why split data across tables

Look at what would happen if orders stored everything inline:

"Maya" and "Mumbai" are written three times already, and that's one customer with three orders. A real shop repeats them thousands of times. Every copy is a chance to be inconsistent (one row says Mumbai, another mumbai, a third Bombay), and every edit means updating every copy. This duplication is exactly the disease that splitting tables cures. The cure has a name, normalization: store each fact once, give it an id, and reference that id everywhere else.

So instead, Maya lives once in customers. The keyboard's price lives once in products. An order just records which customer bought which product:

-- one row in orders, not a wall of repeated text
INSERT INTO orders (customer_id, product_id, quantity)
VALUES (1, 1, 1);

Customer 1, product 1, quantity 1. To get "Maya bought a Keyboard" back, you join the tables on those ids, which we'll do live in a minute.

Primary key vs foreign key

Two kinds of key make this work, and they're easy to mix up.

A primary key uniquely identifies a row in its own table. customers.id is the primary key of customers: every customer has exactly one, no two share it, and it never points anywhere else. It's the row's permanent name tag.

A foreign key is a column in one table that holds the primary key of another table. orders.customer_id is a foreign key: it stores a value that must match some customers.id. It's not the order's identity (that's orders.id). It's the order's reference to a customer.

Read the diagram: customers and products each own a primary key (PK), and orders carries two foreign keys (FK) that point back at them. The crow's-foot end (the little fork) marks the "many" side. One customer, many orders.

One-to-many: one customer, many orders

This is the most common relationship you'll model. One row on the left side relates to many rows on the right. One customer places many orders. One product appears in many orders. One blog post has many comments. Same shape every time.

The "many" side holds the foreign key. That's the rule worth memorizing: the foreign key lives on the table that has many of them. An order belongs to one customer, so customer_id sits in orders. A customer doesn't store a list of order ids. The orders point up at the customer instead.

Run this and watch one customer fan out into several orders:

Maya comes back with 3 orders, Aarav with 2, and Rohan with 0. (We use a LEFT JOIN so Rohan still shows up with a zero instead of vanishing. A plain join would drop customers who've never ordered. That's the joins lesson paying off.) The point: one customers row, many matching orders rows, all linked by that single customer_id pointer.

Many-to-many: the join table

Now a trickier one. A customer can buy many products, and a product can be bought by many customers. That's many-to-many, and neither table can hold a foreign key for it. You can't stuff a list of product ids into a single customers column without breaking the "one value per cell" rule that keeps SQL sane.

The answer is a third table that sits in the middle and records each pairing as its own row. It's called a join table (or junction table), and you already have one: orders. Each order is one customer paired with one product. Many such rows give you a many-to-many relationship, built out of two one-to-many relationships pointing inward.

So orders does double duty. It's a real entity (an order has a quantity and a date), and it's also the bridge between customers and products. Join through it and you can answer questions that touch both ends at once:

Ten rows come back, every order resolved into a real customer name and a real product name. You started with two id numbers per order and turned them into "Maya bought a Keyboard, quantity 1" by following both foreign keys at once. That's the relational model doing its whole job in three lines.

Declaring a foreign key in CREATE TABLE

Everything so far worked on convention: we agreed customer_id points at customers.id. But nothing stopped us from inserting an order for customer 999, who doesn't exist. You can tell the database to enforce the link for you. That's a FOREIGN KEY clause in CREATE TABLE:

CREATE TABLE reviews (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER,
  product_id  INTEGER,
  rating      INTEGER,
  FOREIGN KEY (customer_id) REFERENCES customers(id),
  FOREIGN KEY (product_id)  REFERENCES products(id)
);

Each FOREIGN KEY (...) REFERENCES table(column) line is a promise: a value in this column must match a row in that table. The database now guards it. This guarantee is called referential integrity: the database refuses to let a foreign key point at a row that isn't there. No orphaned orders, no reviews for customers who don't exist, no silent data rot.

Here's the constraint catching a bad insert. We add a reviews table with foreign keys, then try to insert a review for customer 999:

You get FOREIGN KEY constraint failed instead of a saved row. The database just stopped you from creating a review tied to a customer who isn't there. Now change 999 to 1 (Maya exists) and run it again. It saves cleanly, because the reference is real. That's referential integrity earning its keep: bad data bounces before it can lie to you later.

ON DELETE: what happens when the target disappears

One more piece. If you delete customer 1, what should happen to her orders? Without instructions, the database protects the link. By default it blocks the delete (ON DELETE RESTRICT) so you can't strand orphaned orders. You can choose other behaviour in the foreign-key clause:

-- delete a customer, and their orders go too
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
 
-- delete a customer, and their orders keep a NULL customer_id
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL

CASCADE is the common pick for owned children: delete an order and its line-items vanish with it, which is usually what you want. SET NULL keeps the rows but cuts the link. The right choice depends on whether the child can meaningfully exist without its parent. Don't reach for CASCADE reflexively. Deleting one row and silently wiping a hundred related ones is a great way to lose data you meant to keep.

Recap and what's next

Relationships are the whole reason it's a relational database. Split your data so each fact lives once (normalization), give each row a primary key, and reference it from elsewhere with a foreign key. One-to-many puts the foreign key on the many side. Many-to-many gets a join table in the middle, which is exactly what orders is, bridging customers and products. Declare FOREIGN KEY in CREATE TABLE and the database enforces referential integrity for you, with ON DELETE deciding what happens when a referenced row goes away. (Just remember the PRAGMA foreign_keys = ON; in SQLite.)

This came right after Creating tables and schema, where we built the tables these keys connect. Next up: those joins do real work scanning rows, and on a big table that gets slow. Indexes and performance is how you make these lookups fast. For the exact rules of foreign keys in the SQLite engine running these playgrounds, the SQLite foreign keys reference is the source of truth.