Foreign Keys
A column that must match a key in another table. Referential integrity as a constraint, not a hope.
Level: beginner
What is it?
A foreign key constrains one or more columns so their values reference a candidate key in another table, or are null when the schema permits it. It encodes a relationship and lets the database reject references to nonexistent parent rows.
Why does it matter?
Foreign keys protect referential integrity across all clients and jobs. Without them, an application may accumulate orphan rows that appear valid locally but point to missing entities.
Mental model
Think of an enrollment row as a claim that a student and a course exist. The database checks those references at write time rather than trusting the form or service that sent the row.
How it works
- The number and compatible types of referencing columns must match the referenced key.
- A foreign key can be composite; then the tuple of values must match a parent key.
- ON DELETE and ON UPDATE policies can reject, cascade, or set referencing values null, depending on the constraint.
- A nullable foreign key often means the relationship is optional; it does not mean an invalid non-null reference is allowed.
- Foreign keys protect integrity but do not automatically create every useful index on the referencing columns.
Steps
- Insert a course into Courses.
- Insert an Enrollment with a course_id that exists; the constraint allows it.
- Attempt to insert a nonexistent course_id; the database rejects it.
- On course deletion, the configured referential action controls whether deletion is blocked or references are changed.
Real-world usage
- Modeling one-to-many and many-to-many relationships.
- Preventing orphan records after deleting parent rows.
- Designing transactional changes across related entities.
Common mistakes
- Assuming frontend validation is enough to guarantee referential integrity.
- Deleting parent data without considering references and intended retention policy.
- Treating a foreign key as authorization; valid references do not prove the caller may access the row.
Interview questions
What is a referential-integrity violation?
A referencing foreign-key value points to no permitted row in the referenced key, and the database rejects or otherwise handles the operation according to its constraint.
Does a foreign key automatically guarantee the caller is authorized to view the parent?
No. It validates data relationships, not user identity or access permissions.