Внешние ключи

В первом уроке вы переходили по внешним ключам вручную: customer_id заказа — это id клиента. Внешний ключ — это столбец, значения которого являются первичными ключами другой таблицы. При создании таблицы его отмечают словом REFERENCES, после которого идут таблица и столбец, на которые он указывает. Например, заметки службы поддержки магазина о клиентах:

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

Теперь база данных следит, чтобы каждый customer_id в таблице был id существующего клиента. Заметка о клиенте 3 допустима; заметка о клиенте 99, которого не существует, отклоняется:

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

В отличие от других сообщений, это не говорит, какой столбец неверен, поэтому значение, которое ни на что не указывает, нужно найти самим. Во внешнем ключе разрешён NULL, если у столбца нет NOT NULL: он ни на что не указывает, как referred_by у клиента, которому никто не порекомендовал магазин.

Проверка работает и в обратную сторону. В прошлом уроке вы видели, что игру, у которой есть заказы, удалить нельзя: на неё указывают строки order_items. Благодаря этому ни одна строка никогда не указывает на то, чего нет. Если вы хотите, чтобы строки, указывающие на удалённую строку, удалялись вместе с ней, напишите ON DELETE CASCADE после REFERENCES customers (id). Используйте это осторожно, только для строк, которые сами по себе не имеют смысла.

У внешнего ключа тот же тип, что и у столбца, на который он указывает, здесь это INTEGER, а имя обычно говорит, куда он указывает: таблица в единственном числе и _id, как в customer_id или game_id. Вам может встретиться и внешний ключ, записанный после последнего столбца, как FOREIGN KEY (customer_id) REFERENCES customers (id); он означает то же самое.

SQLite проверяет внешние ключи, только если ему об этом сказать командой PRAGMA foreign_keys = ON. В этом курсе она выполняется за вас перед каждым запуском; другие базы данных проверяют их всегда. А новая таблица не появится на вкладке «Таблицы», где показаны только таблицы, с которыми магазин начинает.

Типичная ошибка: опечатка в названии таблицы после REFERENCES. CREATE TABLE её не проверяет, поэтому ошибка обнаруживается только при первом 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