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_idcolumn (a foreign key) that points tobook_idin 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:
- The value in the foreign key column must exist in the referenced primary key column (or be NULL, if allowed)
- You typically cannot delete a row from the parent table if child rows reference it
- 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.