How does the database maintain referential integrity between two tables?
The database keeps a link between two tables honest by checking it every time the data changes.
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 there are three moments when the engine checks. When a row is added to the child table, the parent row has to be there. When a row in the child table is changed, the new parent has to be there. And when a row in the parent table is removed or its key is changed, the engine has to work out what to do about the children that point at it.
That last one is where the rule comes in, and there are four of them. The RESTRICT rule refuses the delete, and it is the default in most engines. The CASCADE rule deletes the children as well. The SET NULL rule puts a missing value in the link, and it needs the column to allow one. And the NO ACTION rule defers the check to the end of the statement, which lets you replace a parent row inside one transaction.
There is one detail that catches everybody out, and it is 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 the other engines it is on by default.
The next two questions in this topic look at what happens when the rule is broken and at how to find the damage.