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

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. В учебных схемах курса цель всегда первичный ключ — это самый распространённый и понятный случай.

Три типа связей: один-к-одному, один-ко-многим, многие-ко-многим

Тип связи определяется одним вопросом: сколько строк одной таблицы может соответствовать одной строке другой — и в каком направлении?

Один-ко-многим — самый частый случай. Одному клиенту может принадлежать много заказов, но каждый заказ относится ровно к одному клиенту. Внешний ключ всегда размещается на стороне «многих»: orders.customer_id живёт в таблице заказов, а не в таблице клиентов. Если customer_id объявлен NOT NULL, у каждого заказа обязательно есть клиент; без NOT NULL заказ теоретически может быть «ничьим».

Один-к-одному — частный случай один-ко-многим с дополнительным ограничением. Представим, что employees хранит основные данные сотрудника, а employee_details — расширенный профиль. Внешний ключ employee_details.employee_id → employees.employee_id сам по себе допускает несколько профилей на одного сотрудника. Чтобы этого не было, столбец внешнего ключа должен быть объявлен с UNIQUE. Тогда одна строка employees может иметь не более одной строки employee_details — и наоборот. Именно UNIQUE превращает связь из один-ко-многим в один-к-одному.

Многие-ко-многим нельзя выразить одним внешним ключом ни в одной из двух таблиц. Один заказ содержит несколько товаров, и один товар входит в несколько заказов. Если добавить product_id прямо в orders, можно хранить только один товар на заказ. Если добавить order_id в products — только один заказ на товар. Оба варианта неправильны.

Решение — таблица-связка: отдельная таблица order_items, где каждая строка хранит пару (order_id, product_id). Оба столбца — внешние ключи к своим таблицам. Нужно добавить товар в заказ — добавляем строку в order_items. Один заказ с тремя товарами даёт три строки; один товар в десяти заказах даёт десять строк. Таблица-связка — единственный реляционный способ реализовать многие-ко-многим.

Чтение схемы учебной базы: таблицы, ключи и все связи

Схема базы данных показывает, какие таблицы существуют, какие столбцы в них есть и куда ведут внешние ключи. Читать её удобно в четыре шага: найти все таблицы → найти в каждой первичный ключ (PK) → проследить каждый внешний ключ (FK) до его цели → определить тип связи.

Учебная база содержит четыре таблицы:

customers:   customer_id (PK), name, email
orders:      order_id (PK), customer_id (FK), order_date, total_amount
products:    product_id (PK), title, price
order_items: item_id (PK), order_id (FK), product_id (FK), quantity

Теперь проследим каждый FK.

orders.customer_id → customers.customer_id. В orders нет UNIQUE на этом столбце, значит у одного клиента может быть много заказов. Каждый заказ с заполненным customer_id ссылается на одного клиента; обязательность связи дополнительно задаётся NOT NULL. Это один-ко-многим: customers — родительская таблица, orders — дочерняя.

order_items.order_id → orders.order_id и order_items.product_id → products.product_id. Каждая строка order_items соединяет один заказ с одним товаром. Один заказ может иметь много строк в order_items с разными product_id — значит, много товаров. Один товар может встречаться в строках order_items с разными order_id — значит, входить во много заказов. order_items и есть таблица-связка, реализующая многие-ко-многим между orders и products.

Итого в учебной базе две связи: customers → orders (один-ко-многим) и orders ↔ products через order_items (многие-ко-многим). Таблица products не имеет прямого внешнего ключа ни к одной другой таблице — она участвует в связи только через order_items.

Этот алгоритм — таблицы, PK, FK, тип — работает для любой схемы, не только учебной.