A foreign key is a column that points at the primary key of another table. It is how a database knows that order 17 belongs to a customer who actually exists, and it is what turns a set of tables into a model of a business. Three relationship shapes cover almost every schema you will design: one-to-many, many-to-many and one-to-one.
How do foreign keys model relationships?
The shop's four tables hold the facts, and the keys between them do the linking. Six customers, nine orders and thirteen order lines make the shape concrete:
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
full_name TEXT NOT NULL,
email TEXT,
city TEXT,
signed_up_on TEXT,
segment TEXT
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
placed_on TEXT,
status TEXT DEFAULT 'placed',
ship_city TEXT
);
CREATE TABLE order_items (
item_id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL REFERENCES orders(order_id),
product_id INTEGER NOT NULL REFERENCES products(product_id),
quantity INTEGER NOT NULL,
unit_price REAL NOT NULL
);
The order_items table carries two of them. Here is what the engine has recorded:
PRAGMA foreign_key_list(order_items);
id seq table from to on_update on_delete match
-- --- -------- ---------- ---------- --------- --------- -----
0 0 products product_id product_id NO ACTION NO ACTION NONE
1 0 orders order_id order_id NO ACTION NO ACTION NONE
Two foreign keys, each naming the table it points at, the local column and the target column. The on_update and on_delete columns are set to NO ACTION, which means the default: the database refuses the change rather than guessing what to do. Those actions are the subject of lesson 48, and the fact that they are visible here is the argument for declaring them deliberately rather than leaving them to chance.
The rows load like this:
INSERT INTO customers (customer_id, full_name, email, city, signed_up_on, segment) VALUES
(1, 'Iris Oyelaran', '[email protected]', 'Bristol', '2023-05-12', 'consumer'),
(2, 'Peter Kuznetsov', '[email protected]', 'Leeds', '2023-09-04', 'business'),
(3, 'Aiko Tanabe', '[email protected]', 'Bristol', '2024-02-19', 'consumer'),
(4, 'Chioma Obi', '[email protected]', 'Bristol', '2024-06-27', 'business'),
(5, 'Felix Baumann', '[email protected]', 'Leeds', '2024-10-08', 'consumer'),
(6, 'Noor Haddad', '[email protected]', 'Manchester', '2025-01-15', 'business');
INSERT INTO orders (order_id, customer_id, placed_on, status, ship_city) VALUES
(1, 1, '2025-01-08', 'shipped', 'Bristol'),
(2, 2, '2025-01-21', 'shipped', 'Leeds'),
(3, 1, '2025-02-14', 'shipped', 'Bristol'),
(4, 3, '2025-03-05', 'shipped', 'Bristol'),
(5, 2, '2025-04-17', 'shipped', 'Leeds'),
(6, 4, '2025-05-09', 'shipped', 'Bristol'),
(7, 5, '2025-06-02', 'shipped', 'Leeds'),
(8, 6, '2025-06-27', 'cancelled', 'Manchester'),
(9, 1, '2025-07-15', 'shipped', 'Bristol');
INSERT INTO order_items (item_id, order_id, product_id, quantity, unit_price) VALUES
(1, 1, 2, 3, 9.95),
(2, 1, 1, 1, 42.5),
(3, 2, 4, 1, 31.75),
(4, 3, 3, 2, 24.0),
(5, 3, 7, 1, 14.5),
(6, 4, 2, 4, 9.95),
(7, 5, 2, 2, 9.95),
(8, 5, 4, 1, 31.75),
(9, 6, 3, 1, 24.0),
(10, 7, 1, 1, 42.5),
(11, 9, 1, 2, 42.5),
(12, 9, 3, 1, 24.0),
(13, 9, 7, 3, 14.5);
Every customer carries a segment as well — consumer, business or institutional — because the same customer master serves the sales reporting in modules 11 and 12, where the mix of a segment is worth more than the name on the order.
What does a one-to-many relationship look like?
One customer has many orders; each order belongs to exactly one customer. The foreign key sits on the many side, and it is a single column — no join table, because the relationship is already captured:
SELECT c.full_name, COUNT(o.order_id) AS orders
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY orders DESC, c.full_name;
full_name orders
--------------- ------
Iris Oyelaran 3
Peter Kuznetsov 2
Aiko Tanabe 1
Chioma Obi 1
Felix Baumann 1
Noor Haddad 1
Iris Oyelaran has three orders and everyone else has one or two. The LEFT JOIN keeps every customer even if they have never ordered — and Noor Haddad, whose only order was cancelled, still appears with a count of one. That is the first practical benefit of the relationship: the customer list and the order list are stored separately and still answer questions about each other.
What about a many-to-many relationship?
An order contains several products and a product appears in several orders. No column can express that on its own: putting product_id on the order would allow only one product per order, and putting order_id on the product would allow only one order per product. The solution is a third table carrying the relationship, with a foreign key to each side — and in this schema that table also carries the facts of the relationship itself, such as quantity and unit price.
SELECT o.order_id, c.full_name, p.name, i.quantity, i.unit_price
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
WHERE o.order_id = 9
ORDER BY i.item_id;
order_id full_name name quantity unit_price
-------- --------------- --------------- -------- ----------
9 Iris Oyelaran Cast Iron Pan 2 42.5
9 Iris Oyelaran Bamboo Tray 1 24.0
9 Iris Oyelaran Linen Tea Towel 3 14.5
Order 9 holds three lines, and each line carries its own quantity and the price paid at the time. That last detail is a design decision worth defending: the price lives on the order line, not only on the product, because the product price changes and yesterday's invoice must not change with it.
And a one-to-one relationship?
Sometimes a table extends another exactly once — a customer profile table with a customer_id that is both the primary key and the foreign key. A table of one-to-one notes on customers would declare customer_id INTEGER PRIMARY KEY REFERENCES customers(customer_id), and the double role of that column is what enforces the "at most one" half. The rule for choosing between one-to-one and one-to-many is worth stating plainly: if the second table describes the first, it is one-to-one; if it records events about the first, it is one-to-many, and most tables that people describe as "details" are really the second kind.
What does enforcing the constraint actually buy?
Try to create an order for a customer who does not exist, with foreign keys switched on:
PRAGMA foreign_keys = ON;
INSERT INTO orders (order_id, customer_id, placed_on) VALUES (50, 99, '2025-10-01');
FOREIGN KEY constraint failed
The insert is refused. Without the constraint the row would have been accepted, and every report joining orders to customers would have shown a blank customer name — a hole in the data that no amount of careful application code guarantees against, because application code has race conditions and databases do not.
One practical warning: SQLite ships with foreign key enforcement switched off. The REFERENCES clause is stored, the documentation is there, and the constraint does nothing until PRAGMA foreign_keys = ON is issued for the connection. MySQL's InnoDB engine enforces them by default; PostgreSQL always has; SQL Server enforces them with NOCHECK disabled by default only if someone explicitly sets it. If your SQLite database is full of orphan rows, this is usually why.
Quick reference
- A foreign key is a column that points at another table's primary key, and it must reference a unique value.
- One-to-many puts the foreign key on the many side; many-to-many needs a junction table; one-to-one doubles the key's role.
- PRAGMA foreign_key_list shows the constraints a table actually has.
- Attributes of the relationship itself — quantity, price paid — belong on the junction table.
- SQLite enforces foreign keys only when PRAGMA foreign_keys = ON is set for the connection.
Relationship drill: the shop wants to record, for each order, the warehouse it was dispatched from, and each warehouse handles many orders — but one specific warehouse is the default for the whole shop. Sketch the two tables and the foreign key, and say which relationship the default setting implies.