Course contentsShow
← System Design
Lesson 300 of 1,9199. SQL vs NoSQL TradeoffsFree lesson

Foreign Keys and Referential Integrity

Using foreign keys to maintain relationships between tables and prevent orphaned records through referential integrity.

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=999 if 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.