Foreign Keys and Referential Integrity
What you'll learn: How foreign keys enforce valid relationships between tables and prevent "orphaned" data that points nowhere.
The Problem
Imagine you have a users table and an orders table. Each order references a user who placed it via a user_id column. What happens if someone deletes a user who has existing orders? You'd end up with orders pointing to a user that no longer exists—these are called orphaned records, and they cause data inconsistency.
What Are Foreign Keys?
A foreign key is a column (or set of columns) in one table that references the primary key of another table. It creates an explicit relationship between the two tables.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
total DECIMAL(10,2),
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
This tells the database: "Every user_id in orders must match an existing user_id in the users table."
Referential Integrity
Referential integrity is the guarantee that relationships between tables remain valid. The database enforces this automatically when you define foreign keys:
- Prevents orphans: You can't insert an order with
user_id=999if user 999 doesn't exist - Protects on delete: By default, you can't delete a user if they have orders (you must handle the orders first)
- Cascade options: You can configure what happens on delete—cascade the delete to child records, set foreign keys to NULL, or reject the operation
This is like a librarian who won't let you check out a book if your library card is invalid, and won't cancel your card while you still have books checked out.
Key Takeaway: Foreign keys enforce referential integrity at the database level, automatically preventing orphaned records and ensuring data relationships remain consistent across tables.