How Do Foreign Keys Protect Relationships?

A foreign key declares that the values in one column must correspond to a row that already exists in another table. It is the mechanism that turns a set of independent tables into a relational model, because it stops the database from accepting an order that points at a customer who does not exist.

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 REFERENCES customers (id),
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES (1, 'Ada Lovelace');
INSERT INTO orders (id, customer_id, amount) VALUES (1, 1, 120.00);

SELECT o.id, c.name, o.amount
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;

One row comes back: order 1 belonging to Ada Lovelace. The order can only exist because the customer it points at exists.

The important detail is the first line. SQLite does not enforce foreign keys by default — the pragma has to be turned on for every connection — and that setting is per connection rather than per database, so an application that opens its own connections has to enable it in its connection setup. PostgreSQL, MySQL and Oracle enforce them out of the box. This is a genuinely common bug: a script that inserts rows in the right order works fine locally, and a test suite that never exercises a violation passes, and the problem only appears in production.

What the constraint buys you is that the invariant holds no matter which code path writes the data. Application-level validation can be skipped by a migration, a bulk import or a second service; the foreign key does not care who is writing. It also gives you the option of an index-friendly join in both directions and lets the planner reason about the relationship.

The cost is a lookup on every insert and update of the referencing column, and that is why a foreign key does not automatically create an index on the referencing side in PostgreSQL — the referencing column needs its own index for the constraint check to be cheap. MySQL and SQLite do create one.

By default the delete is rejected. The constraint says a child row cannot exist without its parent, so removing the parent would break that promise and the engine refuses. The alternative is to declare the intended behaviour on the constraint itself, and ON DELETE CASCADE is the most common choice for ownership relationships.

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 REFERENCES customers (id) ON DELETE CASCADE,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

DELETE FROM customers WHERE id = 1;

SELECT (SELECT COUNT(*) FROM customers) AS customers_left,
       (SELECT COUNT(*) FROM orders) AS orders_left;

The counts come back as 1 customer and 1 order. Ada and both of her orders are gone; Grace and her single order remain.

Without the ON DELETE CASCADE clause, the same DELETE would have failed with a foreign key violation and nothing would have been removed. That default is the safe one, because it stops you destroying data you did not mean to destroy.

There are four behaviours to choose between, and the choice is a business decision rather than a technical one. RESTRICT, the default, refuses the delete. SET NULL sets the child's foreign key to NULL, which keeps the child but orphans it — this only works if the column is nullable, and it means the child record loses its parent. SET DEFAULT sets the column to its declared default, which is rarely what anyone wants. CASCADE deletes the children as well, which is right for an ownership relationship where the child has no meaning without the parent.

Two cautions about CASCADE. It deletes rows you may not have counted, and it fires the delete triggers on the child table, so an audit trail written by a trigger will record the deletions too — which is usually correct but occasionally surprising. And a cascade can cascade further: if a third table references orders, deleting a customer can wipe out a whole subtree. That is why large schemas often prefer a soft delete on the parent and a scheduled cleanup, so that the destruction is visible, auditable and reversible.