After this lesson you can look at two tables, say how they are related, and follow a foreign key by hand from one row to the matching row in another table. That skill is the whole idea behind joins, which start in the next lesson.
All the examples use the sample company database from this course. If you have not loaded it yet, see the lesson on the sample database.
Why data is split into tables
Imagine one giant spreadsheet with a row per employee and columns for the department name, the department city, and the manager. Every engineer would repeat "Engineering" and "Pune". If the department moved to another city, you would have to fix every one of those rows, and if you missed one, your data would disagree with itself.
A relational database avoids this by giving each kind of thing its own table. Departments live in departments, people in employees. An employee row stores only a small number, the id of the department, instead of copying its details. The department's name and city exist in exactly one place.
SELECT * FROM departments;+----+-------------+-----------+
| id | name | city |
+----+-------------+-----------+
| 1 | Engineering | Pune |
| 2 | Sales | Mumbai |
| 3 | Marketing | Delhi |
| 4 | Finance | Mumbai |
| 5 | Support | Pune |
| 6 | Research | Bengaluru |
+----+-------------+-----------+Primary keys and foreign keys
Every row in departments has an id that no other row shares. That column is the primary key: it identifies a single row.
Now look at a few employees:
SELECT id, first_name, department_id FROM employees WHERE id <= 6;+----+------------+---------------+
| id | first_name | department_id |
+----+------------+---------------+
| 1 | Meera | 1 |
| 2 | Arjun | 1 |
| 3 | Kavya | 1 |
| 4 | Rohan | 1 |
| 5 | Isha | 1 |
| 6 | Vikram | 2 |
+----+------------+---------------+The department_id column in employees is a foreign key. It does not identify the employee. It stores the primary key of a row in another table. Employee 6, Vikram, has department_id 2, so he belongs to the department whose id is 2 in departments, which is Sales.
You can follow that link yourself with two small queries:
SELECT department_id FROM employees WHERE id = 6;
SELECT id, name, city FROM departments WHERE id = 2;+---------------+
| department_id |
+---------------+
| 2 |
+---------------+
+----+-------+--------+
| id | name | city |
+----+-------+--------+
| 2 | Sales | Mumbai |
+----+-------+--------+The first query gives the number 2; you carry it to the second query by hand. A join does exactly this lookup for every row at once, and you will write one in the next lesson.
One-to-many
The link between departments and employees is one-to-many: one department can have many employees, but each employee has at most one department. The "many" side holds the foreign key. That is why department_id sits in employees and not the other way round.
You can see the shape by counting employees for each value of the foreign key:
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
ORDER BY department_id;+---------------+-----------+
| department_id | headcount |
+---------------+-----------+
| NULL | 1 |
| 1 | 6 |
| 2 | 5 |
| 3 | 3 |
| 4 | 2 |
| 5 | 3 |
+---------------+-----------+Projects follow the same pattern: each project has a department_id for the department that owns it, so one department can own many projects.
Many-to-many and the linking table
Employees and projects are different. Rohan works on more than one project, and every project has several people. Neither table can hold a single foreign key for this, so a third table, assignments, holds pairs: an employee id, a project id and the hours worked. Each assignment row is a link, and the pair of the two ids is its primary key.
SELECT employee_id, project_id, hours FROM assignments WHERE employee_id = 4;+-------------+------------+-------+
| employee_id | project_id | hours |
+-------------+------------+-------+
| 4 | 1 | 410 |
| 4 | 5 | 40 |
+-------------+------------+-------+Employee 4 appears twice, once for project 1 and once for project 5. Looking up those project ids gives the names:
SELECT id, name FROM projects WHERE id IN (1, 5);+----+-------------------+
| id | name |
+----+-------------------+
| 1 | Customer Portal |
| 5 | Billing Migration |
+----+-------------------+A linking table is the standard solution for many-to-many data. It has two foreign keys, one to each side.
A table that points to itself
A foreign key may even point into its own table. In employees, manager_id holds the id of another employee:
SELECT id, first_name, manager_id FROM employees WHERE id <= 5;+----+------------+------------+
| id | first_name | manager_id |
+----+------------+------------+
| 1 | Meera | NULL |
| 2 | Arjun | 1 |
| 3 | Kavya | 1 |
| 4 | Rohan | 2 |
| 5 | Isha | 2 |
+----+------------+------------+Meera (1) has no manager, so her manager_id is NULL. Arjun (2) and Kavya (3) both report to employee 1. Rohan (4) and Isha (5) report to employee 2. This is called a self-reference, and the lesson on self joins shows how to query it.
Gaps are allowed
The foreign key rules say that a value, when present, must exist in the parent table. They do not say a value must be present. Two cases in the sample data matter for later lessons:
- Employee 18, Ravi, has
NULLindepartment_id. He is a consultant with no department. - Department 6, Research, has no employees at all.
SELECT id, first_name, department_id FROM employees WHERE department_id IS NULL;
SELECT d.id, d.name FROM departments d WHERE d.id NOT IN (SELECT department_id FROM employees WHERE department_id IS NOT NULL);+----+------------+---------------+
| id | first_name | department_id |
+----+------------+---------------+
| 18 | Ravi | NULL |
+----+------------+---------------+
+----+----------+
| id | name |
+----+----------+
| 6 | Research |
+----+----------+The second query uses a subquery, a topic covered later in this course; read it as "departments whose id never appears as an employee's department". These two rows are the ones a careless join will lose. Keep them in mind: the lessons on LEFT and FULL joins use them on purpose.
What the database enforces
When a foreign key is declared, the database refuses changes that would break the link. Inserting an employee that points to department 99, which does not exist, fails with a foreign key error. The sample database declares its keys, so the next statement is rejected. The exact error message depends on the database, so it is shown here without output:
INSERT INTO employees (id, first_name, last_name, email, department_id, job_title, salary, hire_date)
VALUES (21, 'Test', 'Person', 'test.person@example.com', 99, 'Tester', 40000, '2025-01-01');Differences between databases
Foreign keys are standard SQL and work in PostgreSQL, SQL Server and MySQL's InnoDB engine. SQLite parses them but only enforces them when you switch the setting on with PRAGMA foreign_keys = ON, which the sample database does at the top. For MySQL's view of keys, see Foreign keys in MySQL.
Common mistakes
- Putting the foreign key on the wrong side. The foreign key goes on the "many" side. Employees carry
department_id; departments do not carry a list of employees. - Trying to store a list in one column. A column such as
project_idsholding "1, 5" breaks searching and joining. Use a linking table instead. - Assuming every foreign key has a value. Optional relationships leave
NULL, as employee 18 shows. - Assuming every parent has children. Department 6 has none, and a query that expects a child will silently skip it.
- Mixing up which
idis which. Every table has its ownid. A value of 2 indepartment_idis not employee 2.
Exercise
Using only single-table queries (no joins yet), find the name and city of the department that Kavya Nair works in. Then list the ids of the projects she is assigned to.
Show one possible answer
SELECT id, department_id FROM employees WHERE first_name = 'Kavya' AND last_name = 'Nair';
SELECT id, name, city FROM departments WHERE id = 1;
SELECT project_id FROM assignments WHERE employee_id = 3;+----+---------------+
| id | department_id |
+----+---------------+
| 3 | 1 |
+----+---------------+
+----+-------------+------+
| id | name | city |
+----+-------------+------+
| 1 | Engineering | Pune |
+----+-------------+------+
+------------+
| project_id |
+------------+
| 1 |
| 2 |
+------------+The first query gives Kavya's id (3) and her foreign key (1). Carry the 1 into departments, and the 3 into assignments. A join would do both lookups in a single query.
Key points
- A relational database stores each kind of thing in its own table and links rows with keys instead of repeating data.
- A primary key identifies one row. A foreign key is a column that holds the primary key of a row in another table.
- One-to-many is the common case (one department, many employees). Many-to-many needs a third linking table, like assignments.
- A foreign key can be NULL (employee 18 has no department), and a parent row can have no children (department 6 has no employees).
- A join is simply the automatic version of looking up a foreign key value in the other table.