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

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

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

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

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

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

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

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

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

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

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

При вставке или изменении строки СУБД проверяет, существует ли строка с указанным category_id. NULL не нарушает внешний ключ, если столбец не объявлен как NOT NULL; если корневые категории запрещены, это ограничение можно добавить.

Действие ON DELETE определяет судьбу дочерних категорий. RESTRICT или NO ACTION запрещают удаление родителя при наличии потомков, CASCADE удаляет их вместе с родителем, а SET NULL превращает их в корневые категории и потому требует допуска NULL.

Самоссылочный внешний ключ не запрещает цикл вида «категория A является родителем B, а B — родителем A». Также он не проверяет свойства всего дерева, потому что стандартное ограничение внешнего ключа сопоставляет строку с существующей строкой, а не анализирует произвольное число уровней.

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

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

В интернет-магазине категории часто меняются, а пользователи регулярно просматривают товары вместе с непосредственным родителем. Рассматривались три варианта: хранить полный путь в строке, использовать отдельную таблицу всех связей предок–потомок или хранить только parent_id.

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

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

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

1. Гарантирует ли самоссылочный внешний ключ отсутствие циклов?

Нет. Ограничение проверяет только наличие строки, на которую указывает parent_id. Цикл из двух или более категорий может состоять из существующих строк и потому не нарушать внешний ключ.

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

2. Что произойдёт с корневыми категориями, если parent_id объявить обязательным?

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

Если корни допустимы, parent_id оставляют nullable. Если вместо NULL используется специальная корневая строка, она должна существовать заранее, а политика удаления и смысл такой записи должны быть явно определены.

3. Когда выбрать ON DELETE CASCADE, а когда ON DELETE RESTRICT?

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

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