As explained, foreign keys impose constraints on data. Foreign keys make sure that data from one table’s column (e.g. Student.studentId) exists in another table's column too (like Enrollment.studentId). Note that this is independent of data type; any data type could be used as foreign key!
Databases can impose other constraints too. Here is a complete overview:
| Constraint | Description | Remarks |
|---|---|---|
| PRIMARY KEY | Uniquely identifies a row and cannot be empty | |
| FOREIGN KEY | Ensures a referenced value exists in the other table | |
| NOT NULL | The column must always have a value | |
| CHECK | A custom condition that each row must satisfy | These kind of checks often happen in thee application code, instead of in the database. |
| DEFAULT | Sets a value automatically when none is provided | Note that empty values are still possible, even when a default is provided |
Foreign keys make sure data referenced in one table actually exists in another. Let’s revisit the example of students, courses, and enrollments:
Student
| studentId | studentName |
|---|---|
| 101 | Jan |
| 102 | Henk |
| 103 | Piet |
| 104 | Jan |
Course
| courseName | instructor |
|---|---|
| Databases | Dr. Bakker |
| Algorithms | Dr. ten Brink |
| Networks | Dr. Jansen |
Enrollment
| studentId | course | finalGrade |
|---|---|---|
| 101 | Databases | 6.5 |
| 101 | Algorithms | 8.5 |
| 102 | Databases | 7.0 |
| 103 | Networks | 6.0 |
| 103 | Databases | 8.0 |
| 104 | Networks | 8.5 |
| 104 | Algorithms | 7.5 |
The Enrollment.studentId is a foreign key to Student.studentId. What would happen if we kick a student out of the school, deleting their entry from the Student table? If we delete studentId = 101 , there are still 2 enrollments for this student. The deletion will fail because it violates the foreign key constraint. To circumvent this, we would have to first delete all enrollments for this student, before deleting the student itself.
In a larger database, that can be quite cumbersome work. There might be a lot of tables with foreign keys, and you’d have to figure out a lot of relations just to delete a single row. To make this easier, foreign keys can be defined with an accompanying action:
| Action | What happens to children | When to use it |
|---|---|---|
NO ACTION |
Delete is blocked | The safe default — forces you to clean up children explicitly |
RESTRICT |
Delete is blocked (immediately) | Same as NO ACTION in practice; only differs inside complex triggers |
CASCADE |
Children are deleted too | When children have no meaning without the parent (e.g. order items, stream records) |
SET NULL |
FK column becomes NULL | When the child can exist without a parent (e.g. an album where the artist record is removed but the album should stay) |
SET DEFAULT |
FK column resets to its default value | Rare — only useful when there is a meaningful fallback FK value |
Before diving into how we can define these tables, their relations, and their constraints using PostgreSQL, let’s jump into the last promised bit of theory: database normalization.
The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*
Built with ❤️ by the HackYourFuture community · Thank you, contributors
Found a mistake or have a suggestion? Let us know in the feedback form.