How does a foreign key constraint protect a relationship between two tables?
It makes the database check that every child row points at a parent row that really exists.
-- SQLite needs this turned on for each connection.
PRAGMA foreign_keys = ON;
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (id)
);
So what does it protect? Three things.
It stops an order being written for a customer that does not exist. It stops a customer being deleted while orders still point at them. And it stops the link being changed to point at nothing.
Without it, all three of those are possible, and the data quietly goes wrong. You find out later, when a report that joins the two tables comes back with fewer rows than you expected, because some of the orders were quietly dropped by the join.
There is one detail here that catches people out, and it is in the first line of the example. In SQLite the checking is off unless you turn it on, and you have to turn it on for every connection. In PostgreSQL and MySQL and SQL Server the checking is on by default.
The next question in this topic looks at what happens when you delete a parent row.