Связь один ко многим в базе данных: схема и SQL

Связь «один ко многим» (1:N) - это самый распространённый тип отношений в реляционных базах данных. Один клиент размещает много заказов, одна категория объединяет много товаров, один преподаватель ведёт много курсов - во всех этих случаях структура данных одна и та же. Разберём, как правильно реализовать её на уровне схемы, почему индекс по внешнему ключу критичен для производительности и где студенты чаще всего допускают ошибки при проектировании. Ниже - интерактивный калькулятор: задайте размеры таблиц и посмотрите, как меняется объём данных и распределение записей.

Что такое связь один ко многим

В реляционной модели данных связь 1:N означает, что каждая строка родительской таблицы может соответствовать нескольким строкам дочерней таблицы, но каждая строка дочерней таблицы ссылается ровно на одного родителя. Формально это выражается через внешний ключ (FOREIGN KEY): дочерняя таблица хранит столбец, значение которого совпадает с первичным ключом (PRIMARY KEY) родительской записи.

Пример: таблица departments (отделы) и employees (сотрудники). В одном отделе работают многие сотрудники, но каждый сотрудник принадлежит ровно одному отделу. Столбец department_id в таблице employees - это внешний ключ, реализующий связь.

$\text{departments}(1) \xrightarrow{\text{FK}} \text{employees}(N)$

Коэффициент разветвления (fan-out) $k$ - среднее число дочерних записей на одного родителя:

$k = \frac{|\text{child}|}{|\text{parent}|}$

При $k = 5$ и 100 родителях дочерняя таблица содержит ~500 строк, суммарно 600. Именно это соотношение визуализирует калькулятор выше: переключите вид «Масштаб таблиц» - и увидите линейный рост.

Рассчитать для своей задачи

Как создать таблицы с FOREIGN KEY в SQL

Минимальная схема для связи 1:N выглядит так:

CREATE TABLE departments (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE employees (
    id            SERIAL PRIMARY KEY,
    full_name     TEXT NOT NULL,
    department_id INT NOT NULL
        REFERENCES departments(id)
        ON DELETE RESTRICT
);

Ключевые детали:

• REFERENCES departments(id) - объявляет внешний ключ; СУБД автоматически проверяет, что вставляемый department_id существует в departments.
• NOT NULL у внешнего ключа обязателен, если каждый сотрудник обязан принадлежать отделу. Без него сотрудник может быть «без отдела» (NULL) - иногда это нужно, но должно быть осознанным решением.
• ON DELETE RESTRICT - запрещает удалить отдел, пока в нём есть сотрудники. Альтернативы: CASCADE (удаляет сотрудников вместе с отделом) и SET NULL (обнуляет department_id у сотрудников).

Рассчитать для своей задачи

Индекс по внешнему ключу: почему он нужен

Большинство СУБД (PostgreSQL, SQLite) не создают индекс по внешнему ключу автоматически - его нужно добавить вручную:

CREATE INDEX idx_employees_department_id
    ON employees(department_id);

Без этого индекса каждый JOIN между departments и employees потребует полного перебора (seq scan) дочерней таблицы. При $N = 10\,000$ строк это незаметно; при $N = 10^6$ - запрос тормозит в десятки раз.

Правило: добавляй индекс по любому столбцу внешнего ключа, который участвует в JOIN или условии WHERE. MySQL/InnoDB создаёт его автоматически - PostgreSQL и SQLite нет.

JOIN-запросы по связи 1:N

Самый частый запрос - вывести родителей вместе с агрегатом по детям:

-- Число сотрудников в каждом отделе
SELECT
    d.name               AS department,
    COUNT(e.id)          AS employee_count
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY employee_count DESC;

LEFT JOIN гарантирует, что отделы без сотрудников попадут в результат с employee_count = 0. Если использовать INNER JOIN, пустые отделы исчезнут - иногда это и нужно, иногда скрывает ошибки данных.

Рассчитать для своей задачи

Второй частый сценарий - загрузить родителя вместе со всеми его детьми (например, категорию с товарами):

SELECT
    c.id,
    c.name         AS category,
    p.id           AS product_id,
    p.title        AS product
FROM categories c
JOIN products p ON p.category_id = c.id
WHERE c.id = 7;

Здесь важно понимать: результат вернёт не одну строку на категорию, а столько строк, сколько товаров в категории. Это нормальное поведение JOIN - приложение само группирует строки в объект.

Нормализация: когда нужна связь 1:N

Типичная ситуация: есть «плоская» таблица с повторяющимися данными.

Данные о клиенте дублируются в каждом заказе - это нарушение второй нормальной формы (2NF). Правильное решение: вынести клиентов в отдельную таблицу и связать через 1:N.

CREATE TABLE customers (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),
    amount      NUMERIC(10,2) NOT NULL
);

Теперь email хранится в одном месте: если клиент его изменит, достаточно обновить одну строку в customers - все заказы автоматически увидят актуальные данные.

Каскадные операции ON DELETE и ON UPDATE

При удалении родительской записи СУБД должна решить, что делать с дочерними:

Выбор зависит от бизнес-логики: для журналов событий CASCADE разумен (нет пользователя - нет его событий), для финансовых записей - почти всегда RESTRICT или перевод в архивный статус.

Частые ошибки

• Нет индекса по FK. После объявления REFERENCES индекс в PostgreSQL не появляется сам - его нужно создать отдельным CREATE INDEX.
• NULL в обязательных FK. Если у каждого сотрудника должен быть отдел - ставь NOT NULL. Без этого возможны «невидимые» записи, которые не попадают в INNER JOIN и трудно обнаруживаются.
• Ручная проверка вместо FOREIGN KEY. Некоторые разработчики не объявляют FK и проверяют ссылочную целостность в коде. Это ненадёжно: прямой SQL или другой сервис может вставить «битую» ссылку, и СУБД не остановит это.
• Путаница LEFT JOIN с INNER JOIN. LEFT JOIN оставляет родителей без детей; INNER JOIN их скрывает. При подсчёте COUNT(*) вместо COUNT(child.id) LEFT JOIN завысит счётчик на 1 для пустых родителей.
• Дублирование данных вместо нормализации. Хранить имя отдела в каждой строке сотрудника - классическое нарушение 2NF: при переименовании отдела придётся обновить тысячи строк.

FAQ

Чем связь 1:N отличается от 1:1 и N:M? В связи 1:1 каждый родитель имеет ровно одного ребёнка (например, пользователь и его профиль). В N:M каждый родитель связан со многими детьми, и каждый ребёнок - со многими родителями (например, студенты и курсы). N:M реализуется через промежуточную (junction) таблицу, которая сама является дочерней для обеих сторон.

Как выбрать тип ON DELETE - CASCADE или RESTRICT? Простое правило: если дочерние записи не имеют самостоятельного смысла без родителя (строки корзины без заказа, теги без статьи), используй CASCADE. Если дочерние записи представляют важные данные (финансовые транзакции, документы), используй RESTRICT и обрабатывай удаление явно в коде.

Нужен ли индекс по первичному ключу родителя? Первичный ключ по умолчанию индексируется в любой СУБД. Дополнительный индекс нужен именно на стороне дочерней таблицы - на столбце внешнего ключа, по которому происходит JOIN.

Коротко

Связь «один ко многим» реализуется через внешний ключ в дочерней таблице: одна строка в parent соответствует многим строкам в child. Объявите FOREIGN KEY для ссылочной целостности, добавьте индекс по FK для быстрых JOIN, выберите стратегию ON DELETE исходя из бизнес-логики. Нормализация на основе 1:N устраняет дублирование и упрощает обновление данных - это фундамент грамотного проектирования реляционных баз.