Course contentsShow
← Data Science
Lesson 1051 of 2,14527. Database Design and OptimizationFree lesson

Introduction to Foreign Keys

Understand how foreign keys establish relationships between tables by referencing primary keys.

Introduction to Foreign Keys

What You'll Learn

You'll learn how foreign keys create relationships between database tables by pointing to primary keys in other tables.


The Core Idea

A foreign key is a column (or set of columns) in one table that references the primary key of another table. Think of it as a "pointer" or "link" between two tables that establishes their relationship.

Real-World Analogy

Imagine a library system:

  • The Books table has a primary key book_id
  • The Loans table tracks who borrowed what
  • The Loans table needs a book_id column (a foreign key) that points to book_id in the Books table

This foreign key ensures every loan record is connected to an actual book. You can't loan a book that doesn't exist in your library!

How Foreign Keys Work

Foreign keys enforce referential integrity—they guarantee that relationships between tables remain valid. When a foreign key references a primary key:

  1. The value in the foreign key column must exist in the referenced primary key column (or be NULL, if allowed)
  2. You typically cannot delete a row from the parent table if child rows reference it
  3. The database prevents "orphaned" records—rows that point to non-existent data

Simple Example Structure

Customers table:
- customer_id (PRIMARY KEY)
- customer_name

Orders table:
- order_id (PRIMARY KEY)
- customer_id (FOREIGN KEY → Customers.customer_id)
- order_date

Here, customer_id in Orders is a foreign key that must match a customer_id in Customers. This links each order to a specific customer.


Key Takeaway: Foreign keys establish and enforce relationships between tables by referencing primary keys, ensuring data integrity and preventing orphaned records across your database.