Every CREATE TABLE statement since Module 7 already declared FOREIGN KEY on department_id, manager_id, employee_id, and project_id. This lesson is where those get explained properly, what they actually enforce, and the ON DELETE/ON UPDATE behavior that decides what happens when a referenced row changes or disappears.
Learning Objectives
- Define referential integrity, and explain exactly what a FOREIGN KEY enforces.
- Choose the right ON DELETE action for a given relationship.
- Explain why SQLite needs an extra step before it actually enforces foreign keys at all.
The Schema, Visualized
Every arrow in this diagram is a FOREIGN KEY: employees.department_id pointing at departments.id, and project_assignments pointing at both employees.id and projects.id. This lesson is what actually enforces those arrows, not just draws them.
- 1departments: id and name. Engineering, Research, Marketing, Sales, and Legal, which has zero employees.
- 2employees: Points at departments via department_id (the arrow), and at itself via manager_id for the self-join, not drawn here to keep the diagram readable.
- 3projects: id and name. Warehouse Bot v2, Night Vision Sensors, Fleet Dashboard.
- 4project_assignments: The many-to-many link table: one row per employee-project pairing, pointing at both employees and projects (dashed arrows).
What FOREIGN KEY Enforces
A FOREIGN KEY requires a column's value to match an existing value in another table's referenced column (almost always its PRIMARY KEY), or be NULL if the column allows it. employees.department_id REFERENCES departments(id) means no employee row can ever store a department_id that doesn't correspond to a real row in departments, the database rejects the INSERT or UPDATE outright if it would.
This is referential integrity: the guarantee that a reference always points at something real. Without it, a typo or a bug could silently store department_id = 99 on an employee even though no department 99 exists, and every JOIN against it would just silently produce no match, with no error anywhere to flag the actual problem.
ON DELETE and ON UPDATE Actions
ON DELETE CASCADE here means deleting an employee automatically deletes their project_assignments rows too, since an assignment with no employee makes no sense to keep around. ON UPDATE follows the identical syntax and the same four options, for when a referenced primary key value itself changes rather than being deleted, rare with surrogate keys that never change, but the same mechanism either way.
| Action | Behavior When the Referenced Row Is Deleted |
|---|---|
| RESTRICT (often the default) | Blocks the delete outright, as long as any row still references it |
| NO ACTION | Functionally the same as RESTRICT on most databases, checked at the end of the statement |
| CASCADE | Deletes the referencing rows too, automatically |
| SET NULL | Sets the referencing column to NULL instead of deleting the row |
ALTER TABLE project_assignments ADD CONSTRAINT fk_assignment_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;
Choosing the Right Action
| Relationship | A Reasonable Choice | Why |
|---|---|---|
| project_assignments -> employees | CASCADE | An assignment with no employee is meaningless, delete it too |
| employees -> departments | RESTRICT | Deleting a department with active employees should probably be blocked, not silently orphan them |
| employees -> employees (manager_id) | SET NULL | A manager leaving shouldn't delete their reports, just clear their manager_id |
There's no universally correct default, the right action depends entirely on what makes sense for that specific relationship. RESTRICT is the safe default when unsure, it simply blocks the delete rather than silently cascading or nulling something unexpectedly.
SQLite: Foreign Keys Are Off by Default
SQLite recognizes FOREIGN KEY syntax but doesn't actually enforce it unless this PRAGMA is run first, for every connection, it's not a one-time database-wide setting. This exists for backward compatibility with older SQLite databases, but it means a FOREIGN KEY that looks correctly declared can silently allow invalid references on SQLite if this line is forgotten.
PRAGMA foreign_keys = ON;
Try It Yourself
Write a FOREIGN KEY on employees.manager_id, referencing employees.id itself, that sets manager_id to NULL when the referenced employee is deleted, on PostgreSQL.
ALTER TABLE employees ADD CONSTRAINT fk_employees_manager FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL;
Frequently Asked Questions
Can a foreign key reference a column that isn't a primary key?
Yes, as long as the referenced column has a UNIQUE constraint (a primary key already implies UNIQUE, which is why it's the most common target). A foreign key can't reference a column with no uniqueness guarantee, there'd be no single row to match against.
What happens if an INSERT tries to use a department_id that doesn't exist?
The FOREIGN KEY constraint rejects it, the INSERT fails with a referential integrity error rather than silently storing an invalid reference. This enforcement is the entire reason to declare it as a FOREIGN KEY instead of a plain, unconstrained INT.
Common Beginner Mistakes
Forgetting PRAGMA foreign_keys = ON on SQLite and assuming the constraint is active
SQLite silently accepts invalid foreign key values without this PRAGMA set for the connection, even though the FOREIGN KEY clause is right there in the schema. It's easy to assume the constraint is working just because it was declared, when SQLite specifically requires this extra opt-in step.
Using CASCADE everywhere without thinking through the consequences
ON DELETE CASCADE on employees -> departments would delete every employee in a department the moment that department is deleted, likely not the intended behavior. CASCADE is correct for genuinely dependent child data like project_assignments, not for every relationship by default.
Best Practices
- Pick ON DELETE deliberately for each relationship, don't default to CASCADE everywhere without considering what should actually happen.
- Run PRAGMA foreign_keys = ON at the start of every SQLite connection that needs real enforcement.
- Create tables in dependency order (referenced table first), foreign keys require the referenced table to already exist.
Interview Questions
What is referential integrity, and what mechanism enforces it in SQL?
Referential integrity is the guarantee that a reference from one table to another always points at a row that actually exists. A FOREIGN KEY constraint is what enforces it, the database rejects any INSERT or UPDATE that would leave the referencing column pointing at a non-existent value.
Write a FOREIGN KEY from project_assignments.project_id to projects.id that deletes assignments automatically when the project is deleted, on MySQL.
ALTER TABLE project_assignments ADD CONSTRAINT fk_assignment_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; — CASCADE here is appropriate because an assignment row referencing a deleted project has no remaining purpose.
Summary
FOREIGN KEY enforces referential integrity: a column's value must match a real row in another table, or be NULL. ON DELETE and ON UPDATE (RESTRICT, NO ACTION, CASCADE, SET NULL) decide what happens when the referenced row changes or disappears, chosen per relationship rather than defaulted blindly. SQLite needs PRAGMA foreign_keys = ON before it actually enforces any of this.
What's Next?
CHECK is next, a constraint that validates a row's own values against an expression, like refusing to let salary go negative.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.