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: the one-to-many, the many-to-many and the one-to-one.
The previous lesson in this module covered "What Makes a Good Primary Key?", and the next lesson in this module is "How Does Normalization Remove Duplication?".
How do the foreign keys model the relationships?
Four of the shop's 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 foreign keys. 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 NONEThere are two foreign keys, and each one names the table that it points at, the local column, and the target column.
The on_update and on_delete columns are set to NO ACTION, which is 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, whether that is the consumer, the business or the institutional, because the same customer master serves the sales reporting in modules 11 and 12, where the mix of segments is worth more than the name on the order.
What does a one-to-many relationship look like?
One customer has many orders, and each order belongs to exactly one customer.
The foreign key sits on the many side, and it is a single column. There is 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 1Iris Oyelaran has three orders, and everybody 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 they 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 that carries the relationship, with a foreign key to each side. And in this schema, that table also carries facts about the relationship itself, such as the quantity and the 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.5Order 9 holds three lines, and each line carries its own quantity and the price that was paid at the time.
That last detail is a design decision worth defending. The price lives on the order line and not only on the product, because the product price changes, and the invoice of yesterday must not change with it.
And a one-to-one relationship?
Sometimes a table extends another exactly once. That is 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 one, it is one-to-one. If it records events about the first one, it is one-to-many, and most of the tables that people describe as "details" are really the second kind.
What does enforcing the constraint actually buy?
You try to create an order for a customer who does not exist, with the 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 failedThe insert is refused.
Without the constraint the row would have been accepted, and every report that joins the orders to the customers would have shown a blank customer name. That is a hole in the data that no amount of careful application code guarantees against, because application code has race conditions and databases do not.
There is 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 does. SQL Server enforces them with NOCHECK disabled by default, unless somebody explicitly sets it.
If your SQLite database is full of orphan rows, this is usually the reason why.
Quick reference
- A foreign key is a column that points at the primary key of another table, and it has to reference a unique value.
- One-to-many puts the foreign key on the many side, many-to-many needs a junction table, and one-to-one doubles the role of the key.
PRAGMA foreign_key_listshows the constraints a table actually has.- Attributes of the relationship itself, such as the quantity and the price paid, belong on the junction table.
- SQLite enforces foreign keys only when
PRAGMA foreign_keys = ONis set for the connection.
Relationship drill: the shop wants to record, for each order, the warehouse that it was dispatched from. 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 of the relationships the default setting implies.