Lecture 23: Linking Tables with Foreign Keys

This lecture introduces the concept of Foreign Keys. If the primary key is the anchor of a table, the foreign key is the chain that links it to another table. This is the concept that puts the “relation” in “relational database”.

What is a Foreign Key?

A foreign key is a column (or a set of columns) in one table that refers to the primary key of another table. The table containing the foreign key is called the child table, and the table containing the primary key it refers to is called the parent table.

An Example: Customers and Orders

Let’s imagine we run an online store. We need to keep track of our customers and the orders they place. We could try to put all this information in one giant table, but that would be very messy. What if a customer places 10 orders? We would have to repeat their name and address 10 times.

A much better way is to use two tables:

1. A `Customers` table.
2. An `Orders` table.

The `Customers` table will have a `CustomerID` primary key. The `Orders` table will store information about each order, and it will also include a column that stores the `CustomerID` of the person who placed the order. This `CustomerID` column in the `Orders` table is our foreign key.

Creating a Foreign Key Relationship

Let’s see how to define this in SQL. First, we create the parent table, `Customers`.

CREATE TABLE Customers (
    CustomerID INTEGER PRIMARY KEY,
    FirstName TEXT,
    Email TEXT
);

Next, we create the child table, `Orders`. We use a special `REFERENCES` keyword to tell the database that the `Customer_ID` column in this table is a foreign key that points to the `CustomerID` column in the `Customers` table.v

CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY,
    OrderDate TEXT,
    TotalAmount REAL,
    Customer_ID INTEGER,
    FOREIGN KEY (Customer_ID) REFERENCES Customers(CustomerID)
);

Enforcing Data Integrity

This foreign key constraint does something very important: it enforces referential integrity. This means:

• You cannot insert an order with a `Customer_ID` that does not exist in the `Customers` table. The database will give you an error, preventing “orphan” records.
• You generally cannot delete a customer from the `Customers` table if they have existing orders in the `Orders` table (though this behavior can be configured).

This ensures our data stays clean, consistent, and reliable.

In the Next Lecture…

Now that we know how to link tables, how do we get information from both of them at the same time? In the next lecture, we will learn about the most important type of `JOIN`, the `INNER JOIN`, which allows us to combine rows from two or more tables based on their related columns.

Lecture 22: The Importance of Primary Keys