Every employee row carries a department_id that is supposed to point at a real department. Nothing in the column itself stops you from typing 99 — a department that does not exist. Without a rule, the database will happily store that dangling reference, and the row becomes an orphan: it claims a parent it does not have.
A foreign key is the rule that prevents this. It declares that a column in one table references the key of another, and it asks the database to enforce the relationship — refusing inserts and deletes that would leave a child pointing at a missing parent. That guarantee has a name: referential integrity, the promise that every link in the data actually lands somewhere.
erDiagram
DEPARTMENTS ||--o{ EMPLOYEES : "has"
DEPARTMENTS {
int id
text name
}
EMPLOYEES {
int id
text name
int department_id
}
The child, the parent, and the key
A foreign key always involves two tables. The table that holds the pointer — here employees, with its department_id column — is the child. The table it points at — departments, whose id is the target — is the parent. The seed schema declares this with FOREIGN KEY (department_id) REFERENCES departments (id) right inside the CREATE TABLE.
The child's department_id is allowed to repeat — many employees share a department — and it is allowed to be NULL, meaning "no department." What it is not allowed to do, once the key is enforced, is hold a value that does not exist as an id in departments. That is the whole point: every non-null reference must resolve to a real parent row.
PRAGMA foreign_keys=ON;
INSERT INTO employees (name, department_id, role, salary, hired_on)
VALUES ('Lars Berg', 3, 'Sales Rep', 495000, '2025-05-10');
SELECT name, role FROM employees WHERE name = 'Lars Berg';
Enforcement is off until you turn it on
Here is the SQLite detail that catches everyone: declaring FOREIGN KEY in a table does not, on its own, enforce it. By default SQLite parses the constraint and then quietly ignores it — you can insert an employee pointing at a department that does not exist, and nothing complains. Enforcement switches on only when you run PRAGMA foreign_keys = ON; for the connection.
Once on, the database refuses any insert or update that would create a dangling reference, and it blocks deletes that would orphan existing children. The seed schema declares the keys; your queries must opt in to actually enforcing them.
flowchart LR
I["INSERT child row"] --> P{"foreign_keys on?"}
P -->|"valid"| OK["inserted"]
P -->|"invalid"| BLOCK["rejected"]
P -->|"pragma off"| SNEAK["inserted anyway"]
style OK fill:#0e7490,color:#fff
style BLOCK fill:#b45309,color:#fff
style SNEAK fill:#b45309,color:#fff
What happens when a parent is deleted
If you delete a department, what becomes of the employees pointing at it? The foreign key's ON DELETE rule decides, and the default is to block the delete — you cannot remove a department that still has employees, which is exactly what prevents orphans.
Two other policies are possible, chosen when the table is created. ON DELETE CASCADE removes the employees along with their department, cascading the deletion downward. ON DELETE SET NULL keeps the employees but blanks their department_id, severing the link instead of losing the row. Each is a deliberate design choice about how tightly parent and child lives are tied.
NULL is allowed; a dangling value is not
A foreign key treats NULL and a wrong number very differently. NULL means the relationship is simply unknown or absent — Omar has no department, and the key permits that. A non-null value that matches no parent, like 99, is a dangling reference, and enforcement rejects it.
The rule is therefore "every non-null value must resolve," not "every row must have a value." If a relationship is truly mandatory, you combine the foreign key with a NOT NULL constraint on the same column, closing both doors at once.
flowchart LR V["child with valid FK<br/>dept 1 exists"] --> OK["linked, kept"] O["child with NULL FK"] --> N["no link, orphaned"] style OK fill:#0e7490,color:#fff style N fill:#b45309,color:#fff
SELECT e.name, e.department_id FROM employees e LEFT JOIN departments d ON e.department_id = d.id WHERE d.id IS NULL;
Seeing the links in a query
A foreign key is also a path you can follow. Joining employees to departments on the key reassembles each employee with their department, and a LEFT JOIN that keeps rows where the partner is missing will surface any orphans — rows whose key found no match on the other side. Omar, whose department_id is NULL, is the example in this seed: he has no department to link to, so he drops out of an inner join and appears only when you go looking for the unmatched. The relationship the key declares is the same relationship the join travels.
SELECT d.name AS department, COUNT(e.id) AS headcount FROM departments d JOIN employees e ON d.id = e.department_id GROUP BY d.name ORDER BY d.name;
Turn enforcement on, then add a valid new employee 'Lena Vik' to the Engineering department (department_id 1), role 'Engineer', salary 710000, hired '2025-03-01'. The insert succeeds because department 1 exists.
PRAGMA foreign_keys=ON; -- insert Lena Vik into department 1 ;
Keep
PRAGMA foreign_keys=ON;as the first line.Department 1 exists, so the reference is valid and the insert is allowed.
PRAGMA foreign_keys=ON;
INSERT INTO employees (name, department_id, role, salary, hired_on) VALUES ('Lena Vik', 1, 'Engineer', 710000, '2025-03-01');
Use a LEFT JOIN to find the orphaned employee — the one whose department_id finds no matching department. Return their name and department_id.
SELECT e.name, e.department_id FROM employees e -- LEFT JOIN departments, keep rows where d.id IS NULL ;
LEFT JOIN keeps every employee even if no department matches.
Filter with
WHERE d.id IS NULLto keep only the unmatched ones.
SELECT e.name, e.department_id FROM employees e LEFT JOIN departments d ON e.department_id = d.id WHERE d.id IS NULL;
This insert tries to add 'Mira Kahn' to department 99, which does not exist. With enforcement on that should be rejected. Fix it so she joins an existing department — 2 (Marketing) — as a 'Marketer' on salary 530000.
PRAGMA foreign_keys=ON;
INSERT INTO employees (name, department_id, role, salary)
VALUES ('Mira Kahn', 99, 'Marketer', 530000);
Department 99 does not exist, so the reference dangles.
Change the department_id to 2, which is a real department.
PRAGMA foreign_keys=ON;
INSERT INTO employees (name, department_id, role, salary) VALUES ('Mira Kahn', 2, 'Marketer', 530000);
If employees.department_id were declared ON DELETE CASCADE, what would happen when you DELETE a department that still has employees?
CASCADE carries the deletion downward: removing the parent also removes the child rows that reference it. The default (no action) blocks the delete; SET NULL keeps the children but blanks the key.
Transfer skill: find every employee who works in a department located in Oslo. You must join employees to departments and filter by the department's city — following the foreign-key link in the answer.
SELECT e.name FROM employees e -- join departments and filter by city = 'Oslo' ;
Join
employees etodepartments done.department_id = d.id.Filter with
WHERE d.city = 'Oslo'— both Engineering and Sales are in Oslo.
SELECT e.name FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.city = 'Oslo';
Recap
- A foreign key declares that a child column references a parent key, enforcing referential integrity.
- The child holds the pointer (
employees.department_id); the parent is the target (departments.id). - In SQLite, enforcement is off by default — run
PRAGMA foreign_keys = ON;to make the constraints bite. - The default ON DELETE rule blocks deleting a parent that still has children; CASCADE and SET NULL are alternatives.
- A
LEFT JOIN ... WHERE parent.id IS NULLfinds orphans whose key points at nothing.
Next you will turn all of this into safe habits — previewing, backing up, and never letting a modification touch more rows than you intended.