Внешние ключи и целостность

В заказах «Пенки» есть столбец customer_id: номер гостя из таблицы customers. Так одна таблица ссылается на другую. В уроке про ограничения мы учили таблицу не принимать плохие строки, а в прошлом уроке меняли схему. Теперь научим базу следить, чтобы ссылки между таблицами вели туда, где кто-то есть.

Номерок в гардеробе

Ты сдаёшь куртку в гардероб и получаешь номерок 42. Номерок сам по себе ничего не значит: это ссылка на крючок с курткой. Хороший гардероб соблюдает два правила. Не выдаёт номерок на крючок, которого нет. И не снимает куртку с крючка, пока у кого-то на руках его номерок.

Столбец orders.customer_id и есть номерок. Правило «номер должен вести на существующего гостя» называют внешним ключом, а саму идею, что все ссылки в базе ведут на настоящие строки, ссылочной целостностью. Теорию разбирают урок про ключи и урок про NULL и целостность.

REFERENCES в схеме «Пенки»

Внешний ключ объявляют словом REFERENCES рядом со столбцом. В «Пенке» так описаны заказы:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  staff_id INTEGER NOT NULL REFERENCES staff(id),
  ordered_at TEXT NOT NULL,
  paid TEXT NOT NULL
);

Таблицу, на которую ссылаются, называют родительской, а ту, где лежит ссылка, дочерней. Здесь customers родитель, orders ребёнок. Проверим, бережёт ли база ссылки. Гостя с номером 99 в «Пенке» нет:

sqlite> PRAGMA foreign_keys;
╭──────────────╮
│ foreign_keys │
╞══════════════╡
│            0 │
╰──────────────╯
sqlite> INSERT INTO orders (customer_id, staff_id, ordered_at, paid) VALUES (99, 3, '2026-10-07 09:00:00', 'карта');
sqlite> SELECT id, customer_id, staff_id FROM orders WHERE customer_id = 99;
╭────┬─────────────┬──────────╮
│ id │ customer_id │ staff_id │
╞════╪═════════════╪══════════╡
│ 13 │          99 │        3 │
╰────┴─────────────┴──────────╯

Заказ на несуществующего гостя прошёл. Ответ на загадку в первой строке: PRAGMA foreign_keys равна 0. Проверка внешних ключей выключена.

PRAGMA foreign_keys = ON

Так сложилось исторически: внешние ключи появились в SQLite в 2009 году, и чтобы не сломать старые программы, их проверку оставили выключенной. Включать её нужно самому:

sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
╭──────────────╮
│ foreign_keys │
╞══════════════╡
│            1 │
╰──────────────╯
sqlite> INSERT INTO orders (customer_id, staff_id, ordered_at, paid) VALUES (98, 3, '2026-10-07 09:05:00', 'карта');
Error near line 3: FOREIGN KEY constraint failed
sqlite> DELETE FROM customers WHERE id = 1;
Error near line 4: FOREIGN KEY constraint failed

Теперь гардероб работает как надо. Номерок на гостя 98 не выдан. И Машу удалить нельзя: у неё три заказа, и они остались бы ссылками в никуда.

Главная ловушка: настройка живёт только в одном соединении, а в файл базы не записывается. Закрыл sqlite3, открыл снова, и проверка опять выключена:

sqlite> PRAGMA foreign_keys;
╭──────────────╮
│ foreign_keys │
╞══════════════╡
│            0 │
╰──────────────╯
sqlite> PRAGMA foreign_key_check(orders);
╭────────┬───────┬───────────┬──────╮
│ table  │ rowid │  parent   │ fkid │
╞════════╪═══════╪═══════════╪══════╡
│ orders │    13 │ customers │    1 │
╰────────┴───────┴───────────┴──────╯
sqlite> DELETE FROM orders WHERE id = 13;

Песочница ведёт себя так же: каждый запуск это новый sqlite3, и PRAGMA foreign_keys в нём снова 0. Поэтому PRAGMA foreign_keys = ON пишут первой строкой при каждом открытии базы, в коде программы тоже.

Вторая команда в этом протоколе, PRAGMA foreign_key_check, ищет строки, которые уже нарушают внешние ключи. Она нашла заказ 13 с гостем 99, который мы записали при выключенной проверке. Включение проверки старые ошибки не исправляет, их приходится находить и чинить руками, что мы и сделали через DELETE.

Что делать при удалении родителя

Запрет удалять Машу это поведение по умолчанию. Иногда нужно другое: удалить гостя вместе с его картой лояльности или удалить гостя, а подарок оставить ничьим. Для этого у REFERENCES есть действие ON DELETE. Заведём две таблицы:

sqlite> CREATE TABLE cards (
(x1...>   number TEXT PRIMARY KEY NOT NULL,
(x1...>   customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE
(x1...> );
sqlite> CREATE TABLE gifts (
(x1...>   id INTEGER PRIMARY KEY,
(x1...>   title TEXT NOT NULL,
(x1...>   customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL
(x1...> );
sqlite> INSERT INTO cards VALUES ('П-0005', 5), ('П-0006', 6);
sqlite> INSERT INTO gifts (title, customer_id) VALUES ('кружка', 6), ('пакет зёрен', 5);

NOT NULL у ключа карты стоит не просто так, помнишь странность из урока 10? Теперь удалим Кирилла, гостя номер 6, у которого нет заказов:

sqlite> PRAGMA foreign_keys = ON;
sqlite> DELETE FROM customers WHERE id = 6;
sqlite> SELECT * FROM cards;
╭────────┬─────────────╮
│ number │ customer_id │
╞════════╪═════════════╡
│ П-0005 │           5 │
╰────────┴─────────────╯
sqlite> SELECT * FROM gifts;
╭────┬─────────────┬─────────────╮
│ id │    title    │ customer_id │
╞════╪═════════════╪═════════════╡
│  1 │ кружка      │ NULL        │
│  2 │ пакет зёрен │           5 │
╰────┴─────────────┴─────────────╯

Карта Кирилла ушла вместе с ним: это CASCADE, каскадное удаление. Кружка осталась, но теперь ничья: SET NULL поставил в ссылку NULL. Без PRAGMA foreign_keys = ON не сработало бы ни то, ни другое. Те же действия есть в PostgreSQL, сравни с уроком про внешние ключи.

С CASCADE будь осторожен: одно удаление может потянуть за собой цепочку из сотен строк в нескольких таблицах. Для заказов оно обычно не подходит, историю продаж стирать нельзя.

Попробуй сам

Песочница работает на свежей базе «Пенки». Включи проверку и попробуй нарушить ссылки. Две команды нарочно падают:

Матча-латте никто не заказывал, поэтому её удаление прошло. Убери первую строку и запусти снова: без PRAGMA обе ошибки исчезнут, а foreign_key_check покажет испорченные ссылки.

Проверь себя

Задание

Дальше

Внешние ключи связали таблицы между собой. Какие бывают связи, один-ко-многим и многие-ко-многим, и как их рисовать таблицами, разбираем в следующем уроке.