SQL Foreign Key Explained

A foreign key is a column (or set of columns) in one table that references the primary key of another table. It's how a relational database enforces referential integrity — it guarantees that a value like customer_id in an orders table can never point to a customer that doesn't actually exist.

Without foreign keys, nothing stops you from inserting an order for customer 9999 even if no such customer exists in your customers table. That's exactly the kind of silent data corruption foreign keys prevent.

Defining a Foreign Key

Here's the two-table setup used throughout this course — a customers table and an orders table where each order belongs to one customer:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50)
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

The FOREIGN KEY (customer_id) REFERENCES customers(id) line tells the database: every value stored in orders.customer_id must already exist as an id in customers. This is exactly what lets you later write a reliable INNER JOIN between the two tables — the relationship is guaranteed to be valid.

What Happens on an Invalid Insert

If you try to insert an order referencing a customer that doesn't exist, the database rejects it instead of silently storing bad data:

INSERT INTO orders (id, customer_id, amount, order_date)
VALUES (501, 9999, 250.00, '2026-01-15');

Result:

ERROR: insert or update on table "orders" violates foreign key constraint
DETAIL: Key (customer_id)=(9999) is not present in table "customers".

This is the database doing exactly what it should — catching a bug before it becomes bad data you have to clean up later.

Deleting a Referenced Row

Foreign keys also protect against deleting a customer that still has orders. By default, trying to delete a referenced customers row that has matching orders rows will fail, unless you explicitly define an ON DELETE behavior (like CASCADE to delete the orders too, or SET NULL to clear the reference). Getting this right matters once you start writing DELETE statements against tables with real relationships — it's worth deciding your cascade strategy up front rather than discovering it the hard way.

---