A database is more than a set of separate tables. Customers place orders, orders contain products, products have tags, students join courses. These connections are called relationships, and getting them right is the most important part of designing a database.
You've already used one relationship: each order belongs to one customer, stored as customer_id in the orders table. That's a one-to-many relationship. But not every connection fits that shape. A product can have many tags, and a tag can apply to many products. Where would you even put the column?
In this lesson you'll learn the three kinds of relationship, how each is built with foreign keys, why many-to-many needs an extra junction table, what should happen when a linked row is deleted, and how to query across these links.
One-to-many
One customer can have many orders, but each order belongs to one customer. Put the foreign key on the "many" side:
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
order_date DATE NOT NULL
);This is the most common relationship by far: one author, many books; one category, many products; one post, many comments. Putting a list of order ids in a column of customers instead would be impossible to search or join properly.
Many-to-many and junction tables
A product can have many tags, and a tag can be on many products. Neither table can hold a single foreign key, so you add a third table whose only job is to record the pairs:
CREATE TABLE product_tags (
product_id INTEGER NOT NULL REFERENCES products(id),
tag_id INTEGER NOT NULL REFERENCES tags(id),
PRIMARY KEY (product_id, tag_id)
);This is a junction table (also called a link, bridge or join table). Each row says "this product has this tag". The composite primary key on both columns stops the same pair being added twice. A junction table can hold extra facts about the pair too, such as the date a student enrolled on a course.
One-to-one
In a one-to-one relationship, each row matches at most one row in another table: a user and their profile settings, or an employee and their parking space. Use a foreign key that is also UNIQUE, or share the same primary key in both tables.
One-to-one tables are less common. They're used to keep rarely needed or sensitive columns separate, such as payroll details, or to split a very wide table. If two tables always appear together, a single table is usually simpler.
What happens on delete
When a customer is deleted, what should happen to their orders? The foreign key can say:
| Option | Effect |
|---|---|
ON DELETE RESTRICT (or NO ACTION) | Refuse to delete a customer who has orders (the default) |
ON DELETE CASCADE | Delete their orders too |
ON DELETE SET NULL | Keep the orders, set customer_id to NULL |
Choose based on meaning. Deleting a product should probably cascade to its rows in product_tags, but deleting a customer should rarely wipe their order history; RESTRICT is the safe choice there. In SQLite, foreign keys are only checked after PRAGMA foreign_keys = ON;.
SQL Relationships Syntax
Relationship patterns
-- one-to-many
child_table.parent_id INTEGER REFERENCES parent_table(id)
-- many-to-many
CREATE TABLE a_b (
a_id INTEGER REFERENCES a(id) ON DELETE CASCADE,
b_id INTEGER REFERENCES b(id) ON DELETE CASCADE,
PRIMARY KEY (a_id, b_id)
);SQL Relationships Summary
- One-to-many: put the foreign key on the "many" side, such as
customer_idin orders. - Many-to-many: add a junction table with two foreign keys and a composite primary key.
- One-to-one: a foreign key that is also unique, used for optional or sensitive details.
- ON DELETE RESTRICT, CASCADE or SET NULL decides what happens to linked rows.
- Query a many-to-many relationship by joining through the junction table.
SQL Relationships Examples
Each example shows the code, the output it produces and what each part does.
Products with a given tag
The tags table holds 1 "back to school", 2 "eco" and 3 "gift idea", and product_tags links them to products.
SELECT p.name AS product, t.name AS tag
FROM products p
JOIN product_tags pt ON pt.product_id = p.id
JOIN tags t ON t.id = pt.tag_id
WHERE t.name = 'gift idea'
ORDER BY p.name;product | tag
-------------+----------
Backpack | gift idea
Desk lamp | gift idea
Water bottle | gift ideaLine-by-line explanation
- Line 3Go from products into the junction table.
- Line 4Then from the junction table to tags.
- Line 5Filter on the tag's name. The junction table connects the two sides.
Each product's tags on one line
SELECT p.name,
group_concat(t.name, ', ') AS tags
FROM products p
JOIN product_tags pt ON pt.product_id = p.id
JOIN tags t ON t.id = pt.tag_id
GROUP BY p.id
ORDER BY p.id;name | tags
-------------+--------------------------
Notebook | back to school
Pen | back to school
Backpack | back to school, gift idea
Water bottle | eco, gift idea
Lunch box | eco
Desk lamp | gift ideaLine-by-line explanation
- Line 2group_concat joins the tag names of each group into one string. MySQL: GROUP_CONCAT(t.name SEPARATOR ', '). PostgreSQL: STRING_AGG(t.name, ', ').
- Line 6One row per product.
Counting the "many" side
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY c.id;name | orders
------+-------
Ana | 2
Ben | 2
Chloe | 1
Dev | 1
Ema | 0Line-by-line explanation
- Line 1COUNT(o.id) counts only real orders, so Ema gets 0 rather than 1.
- Line 3A LEFT JOIN keeps customers with no orders.
Common SQL Relationships Mistakes
A list of ids in one column
CREATE TABLE products (id INTEGER, name TEXT, tag_ids TEXT);
-- tag_ids = '1,3' impossible to join or checkCREATE TABLE product_tags (
product_id INTEGER REFERENCES products(id),
tag_id INTEGER REFERENCES tags(id),
PRIMARY KEY (product_id, tag_id)
);The foreign key on the wrong side
ALTER TABLE customers ADD COLUMN order_id INTEGER; -- only one order per customer-- orders.customer_id: each order points at its one customerCASCADE where history matters
customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE
-- deleting a customer erases years of sales recordscustomer_id INTEGER REFERENCES customers(id) ON DELETE RESTRICTSQL Relationships in Real Projects
Relationships everywhere
Online shops link orders to customers (one-to-many), orders to products through order lines (many-to-many with extra columns such as quantity and price), and users to their settings (one-to-one). Social networks store follows and likes in huge junction tables, and school systems link students to courses with enrolment tables that also hold grades.
SQL Relationships FAQ
What is a junction table also called?
You'll see several names for the same idea: junction table, join table, bridge table, link table and associative table. Whatever the name, it holds pairs of foreign keys, such as product_id and tag_id, to represent a many-to-many relationship.
Should a junction table have its own id column?
Either design works. A composite primary key on the two foreign keys, PRIMARY KEY (product_id, tag_id), prevents duplicate pairs automatically. A separate id is convenient when the relationship has its own data and other tables refer to it; then add a UNIQUE constraint on the pair.
Can a table have a relationship with itself?
Yes. An employees table can have a manager_id column that references employees.id, and a categories table can have a parent_id for subcategories. Query one level with a self join, as in the advanced joins lesson, and whole trees with a recursive CTE.
What is cardinality in a database relationship?
Cardinality describes how many rows on one side can relate to rows on the other: one-to-one, one-to-many or many-to-many, plus whether the relationship is optional. It decides where the foreign key goes and whether you need a junction table.
How are relationships drawn in diagrams?
Entity-relationship diagrams show tables as boxes and relationships as lines. The common "crow's foot" notation draws a three-pronged foot on the "many" end and marks whether each side is optional. The ER diagrams lesson explains how to read and draw them.
SQL Relationships Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Complete the junction table's composite primary key.
CREATE TABLE product_tags (
product_id INTEGER REFERENCES products(id),
tag_id INTEGER REFERENCES tags(id),
PRIMARY KEY ()
);In a one-to-many relationship, where does the foreign key go?
Students can take many courses, and courses have many students. What do you need?
What does ON DELETE CASCADE do?
Using the tag data in this lesson, how many rows does SELECT * FROM product_tags WHERE tag_id = 1; return?
SQL Relationships Quiz
5 questions to check what you learned.
Which relationship is "one customer, many orders"?
- One-to-one
- Many-to-many
- Self join
- One-to-many
What stops the same product and tag being linked twice in a junction table?
- A composite primary key (or UNIQUE constraint) on both columns
- An index on product_id
- ON DELETE CASCADE
- NOT NULL
What's the usual default behaviour when you delete a row that other rows reference?
- The other rows are deleted
- The references become NULL
- The delete is refused
- Nothing is checked
What's another name for a junction table?
- View
- Link or bridge table
- Index
- Temporary table
What does an entity relationship diagram show?
- Query speed
- Rows of data
- User permissions
- Tables and the relationships between them