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

Связываем таблицы внешними ключами и защищаем согласованность данных.

Внешний ключ (FOREIGN KEY) — это столбец, значения которого обязаны существовать в другой таблице; он не даёт ссылаться на несуществующую строку.

Зачем нужны внешние ключи

Представьте таблицы авторов и книг. У книги есть столбец author_id, ссылающийся на автора. Без внешнего ключа в author_id можно случайно записать номер несуществующего автора — данные станут несогласованными. Внешний ключ запрещает такие «висячие» ссылки и заодно мешает удалить автора, у которого ещё есть книги.

Важно: в SQLite проверку надо включить

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

sqlite3 shop.db
sqlite> PRAGMA foreign_keys = ON;

Без этого внешние ключи объявить можно, но база не будет их проверять. Многие библиотеки и драйверы включают этот PRAGMA автоматически, но если вы работаете напрямую — не забывайте про него.

Объявление и работа

Внешний ключ задают через REFERENCES таблица(столбец). Свяжем книги с авторами:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE books (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (id, name) VALUES (1, 'Толстой'), (2, 'Чехов');
INSERT INTO books (title, author_id) VALUES
    ('Война и мир', 1),
    ('Каштанка',    2);

SELECT title, author_id FROM books;

Вывод:

Война и мир|1
Каштанка|2

Что защищает внешний ключ

Когда проверка включена, попытка сослаться на несуществующего автора будет отклонена:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books  (id INTEGER PRIMARY KEY, title TEXT,
                     author_id INTEGER REFERENCES authors(id));

INSERT INTO authors (id, name) VALUES (1, 'Толстой');

-- автора с id = 999 не существует
INSERT INTO books (title, author_id) VALUES ('Сирота', 999);

Вывод:

Runtime error: FOREIGN KEY constraint failed

Так же база не даст удалить автора, на которого ещё ссылаются книги. Можно настроить поведение при удалении родителя: ON DELETE CASCADE удалит и связанные книги, а ON DELETE SET NULL обнулит ссылку.

Итог

• Внешний ключ REFERENCES связывает таблицы и запрещает ссылки на несуществующие строки.
• В SQLite проверка ключей выключена по умолчанию — включайте PRAGMA foreign_keys = ON.
• ON DELETE CASCADE / SET NULL задают поведение при удалении родительской строки.