Внешние ключи и схема базы данных

FOREIGN KEY в SQLite: минимальный рабочий пример

Внешний ключ связывает столбец дочерней таблицы с ключом родительской. Для заказа это означает: orders.customer_id должен указывать на существующего клиента. В SQLite сначала включите проверку ограничений для текущего подключения, затем объявите ссылку при создании таблицы:

PRAGMA foreign_keys = ON;
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);
INSERT INTO customers VALUES (7);
INSERT INTO orders VALUES (101, 7);

Последняя вставка проходит: клиент 7 есть. Попытка вставить заказ с customer_id=8 при включённой проверке завершится FOREIGN KEY constraint failed. Команда PRAGMA foreign_keys; должна вернуть 1; значение 0 означает, что проверка в этом подключении выключена. Выполните PRAGMA foreign_keys = ON до начала транзакции: внутри открытой транзакции переключение не действует. Обязательность ссылки обеспечивает отдельное NOT NULL; без него NULL в дочернем столбце допустим. Разбор типов связей ниже поможет выбрать ключи для вашей схемы, а задача на вставку с внешним ключом проверит понимание.

До этого урока мы работали с отдельными таблицами и агрегировали данные внутри одной из них. Во многих реальных базах данные распределены между несколькими таблицами, и данные в них не изолированы: клиент связан с заказами, заказ — с товарами. Именно механизм этой связи — тема урока.

Внешний ключ: ссылка между таблицами и ссылочная целостность

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

Возьмём пример из учебной базы. В таблице orders есть столбец customer_id. Он не просто число — это ссылка на customers.customer_id. Если в строке заказа customer_id = 7, значит этот заказ принадлежит клиенту с первичным ключом 7. Убери строку с клиентом 7 из customers — и ссылка в orders станет «висящей», то есть будет указывать в никуда.

Чтобы база данных не допускала таких ситуаций, объявляют ограничение внешнего ключа (FOREIGN KEY constraint). Оно запрещает вставить в orders значение customer_id, которого нет в customers. При удалении клиента поведение задаётся схемой: без специального действия удаление связанной строки обычно блокируется, ON DELETE CASCADE удаляет дочерние строки, а ON DELETE SET NULL очищает ссылку, если столбец допускает NULL. Это называется ссылочной целостностью — гарантией того, что ни одна ссылка не ведёт в пустоту.

В SQLite это ограничение объявлено в схеме, но не проверяется по умолчанию. Чтобы включить проверку для текущего соединения, нужно выполнить:

PRAGMA foreign_keys = ON;

Без этой команды SQLite молча примет строку с несуществующим customer_id — никакой ошибки не будет. Это историческое решение для обратной совместимости, и оно отличает SQLite от большинства других СУБД, где ссылочная целостность включена по умолчанию.

В общем случае внешний ключ может ссылаться не только на первичный ключ, но и на любой столбец с ограничением UNIQUE. В учебных схемах курса цель всегда первичный ключ — это самый распространённый и понятный случай.