Для иерархии категорий внешний ключ допускает, чтобы категория стала родителем самой себя. Какое ограничени...

CREATE TABLE category (
    category_id INTEGER PRIMARY KEY,
    parent_id INTEGER,
    name VARCHAR(100) NOT NULL,
    FOREIGN KEY (parent_id)
        REFERENCES category(category_id)
);

Краткий ответ

Добавьте проверку, запрещающую равенство идентификаторов категории и её родителя:

ALTER TABLE category
ADD CONSTRAINT category_not_own_parent
CHECK (parent_id IS NULL OR parent_id <> category_id);

Внешний ключ гарантирует только существование указанного родителя. Он не запрещает ссылаться на ту же строку, поэтому для запрета непосредственной самоссылки нужен отдельный CHECK.

Исторический контекст

Реляционная модель разделяет разные виды целостности: ключи идентифицируют строки, внешние ключи проверяют связи между таблицами, а проверки строковых значений задаются ограничениями CHECK. Такой подход позволяет переносить простые бизнес-правила из прикладного кода в схему базы данных.

Это особенно важно для иерархических данных: приложение может быть не единственным клиентом базы, а значит, правило должно соблюдаться при любом способе записи — через API, импорт или административный SQL-запрос.

Постановка проблемы

Самоссылочный внешний ключ в исходной схеме проверяет, что значение parent_id существует в category. При вставке строки (category_id = 10, parent_id = 10) это условие выполнено: категория с идентификатором 10 уже существует либо создаётся в допустимом для СУБД порядке.

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

Подробное решение

Ограничение CHECK (parent_id IS NULL OR parent_id <> category_id) разрешает два случая: у корневой категории родитель отсутствует, либо идентификаторы категории и родителя различаются. Внешний ключ при этом продолжает гарантировать, что ненулевой parent_id ссылается на существующую категорию.

Проверку на NULL важно записать явно. В SQL результат сравнения NULL <> category_id имеет значение UNKNOWN, а строковое CHECK обычно считается пройденным, если его результат не равен FALSE. Поэтому один лишь вариант CHECK (parent_id <> category_id) не выражает правило достаточно явно для nullable-столбца.

Это ограничение предотвращает только цикл длины один. Оно не запрещает цикл из нескольких строк: например, категория 1 может ссылаться на 2, а 2 — на 1. Для запрета произвольных циклов потребуется более сложное решение: триггер с обходом графа, периодическая проверка, хранение пути или специализированная модель иерархии.

Ситуация из практики

В каталоге интернет-магазина категории редактируются через API и загружаются пакетным импортом. При ошибке импорта одна категория получила собственный parent_id, из-за чего сервис построения меню зациклился.

Рассматривались варианты:

• проверять связь только в приложении — просто реализовать, но другие клиенты базы смогут обойти правило;
• добавить CHECK на неравенство идентификаторов — надёжно и дёшево для запрета непосредственной самоссылки;
• использовать триггер — позволяет проверять также циклы из нескольких узлов, но сложнее, дороже при массовых изменениях и требует аккуратной работы с рекурсивными запросами.

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

Что кандидаты часто упускают

1. Предотвращает ли такой CHECK цикл из двух категорий?

   Нет. Условие сравнивает только parent_id и category_id одной строки. Оно отклонит 1 → 1, но пропустит 1 → 2 и 2 → 1, поскольку в каждой строке идентификаторы различаются. Для контроля транзитивных связей требуется анализ цепочки предков.

2. Зачем оставлять внешний ключ, если уже есть CHECK?

   Эти ограничения решают разные задачи. CHECK запрещает локально недопустимое равенство, но не проверяет наличие строки с указанным родителем. Внешний ключ не допускает ссылку на несуществующую категорию; нужны оба ограничения.

3. Что изменится, если parent_id объявить NOT NULL?

   Корневые категории больше нельзя будет представить отсутствующим родителем. Тогда простая проверка CHECK (parent_id <> category_id) станет достаточной с точки зрения NULL, но сама модель потребует специального способа обозначать корень, например отдельной корневой строки. Обычно nullable-внешний ключ естественнее выражает необязательного родителя.