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

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

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

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

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

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

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

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

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

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

Если правило сводится к уникальности ключа для некоторого подмножества строк, его следует выразить уникальным ограничением или частичным уникальным индексом. Для PostgreSQL пример может выглядеть так:

CREATE TABLE department_assignment (
    department_id INTEGER NOT NULL,
    employee_id INTEGER NOT NULL,
    role TEXT NOT NULL
);

CREATE UNIQUE INDEX one_manager_per_department
    ON department_assignment (department_id)
    WHERE role = 'manager';

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

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

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

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

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

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

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

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

1. Может ли CHECK содержать запрос к другим строкам и решить задачу таким способом?

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

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

2. Почему триггер без блокировок может не устранить гонку?

Две транзакции могут одновременно проверить отсутствие руководителя и каждая увидеть допустимое состояние. Если затем обе вставят строку, проверки обеих транзакций завершатся успешно, хотя итоговое состояние нарушит правило.

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

3. Когда уникальный индекс не заменяет триггер?

Индекс хорошо выражает точное условие уникальности ключа: например, один руководитель на отдел. Он не подходит для правил вроде «сумма долей сотрудников отдела должна быть равна ста процентам» или «интервалы одного сотрудника не должны пересекаться», если СУБД не предоставляет специального ограничения для таких данных.

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