Foreign Keys

In lesson 1, you followed foreign keys by hand: the customer_id of an order is the id of a customer. A foreign key is a column whose values are the primary keys of another table. When you create a table, you mark it with REFERENCES, followed by the table and the column it points at. Notes of the shop's support about its customers, for example:

CREATE TABLE customer_notes (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers (id),
    note TEXT NOT NULL
) STRICT;

From now on, the database makes sure that every customer_id in the table is the id of a customer who exists. A note about customer 3 is fine; a note about customer 99, who doesn't exist, is refused:

INSERT INTO customer_notes (customer_id, note)
VALUES (3, 'Prefers puzzle games.');

INSERT INTO customer_notes (customer_id, note)
VALUES (99, 'Asked about a refund.');

Error on line 4: FOREIGN KEY constraint failed

Unlike the other messages, this one doesn't say which column is wrong, so you have to find the value that points nowhere yourself. NULL is allowed in a foreign key, unless the column has NOT NULL: it points at nothing at all, like the referred_by of a customer nobody recommended the shop to.

The check works the other way too. In the last lesson, you saw that a game with orders can't be deleted: the rows of order_items point at it. Thanks to that, no row ever points at something that isn't there. If you'd rather have the rows that point at a deleted row deleted with it, write ON DELETE CASCADE after REFERENCES customers (id). Use it carefully, only for rows that make no sense on their own.

A foreign key has the type of the column it points at, here INTEGER, and its name usually says where it points: the table in the singular and _id, as in customer_id or game_id. You may also meet a foreign key written after the last column, as FOREIGN KEY (customer_id) REFERENCES customers (id); it means the same.

SQLite checks foreign keys only when it's told to, with PRAGMA foreign_keys = ON. This course does that for you before every run; other databases check them always. And the new table won't appear in the Tables tab, which shows only the tables the shop starts with.

Typical mistake: a typo in the table after REFERENCES. CREATE TABLE doesn't check it, so the mistake shows only at the first INSERT:

CREATE TABLE customer_notes (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customer (id),
    note TEXT NOT NULL
) STRICT;

INSERT INTO customer_notes (customer_id, note)
VALUES (3, 'Prefers puzzle games.');

Error on line 7: no such table: main.customer