Составной внешний ключ для связанных объектов

Обработчик создания проекта уже назначает организацию на сервере. Теперь добавим задачи проекта и увидим другую разновидность ошибки. Можно правильно назначить задаче компанию «Юг», но передать ей UUID проекта «Севера». Каждая строка сама по себе выглядит допустимой, а связь между строками нарушает границу.

Такое нарушение следует запретить самой базой. Проверка в HTTP-обработчике остаётся нужна для понятного ответа, но будущий импорт или фоновой процесс могут записывать задачи иначе. В этом уроке разберём составной внешний ключ, который сравнивает одновременно компанию и идентификатор проекта. SQL находится в sql/01-schema.sql полного примера; выполнение схемы при подготовке не проводилось.

Два значения описывают одну ссылку

Первичный ключ проектов уже состоит из (organization_id,id). Поэтому дочерняя строка хранит обе части адреса родителя. Ниже приведено целое определение таблицы tasks из схемы. Поле organization_id имеет тот же смысл, что у проекта, а не обозначает автора задачи.

CREATE TABLE tasks (
  organization_id uuid NOT NULL,
  id uuid NOT NULL,
  project_id uuid NOT NULL,
  title text NOT NULL CHECK(length(title) BETWEEN 1 AND 240),
  PRIMARY KEY (organization_id,id),
  FOREIGN KEY (organization_id,project_id)
    REFERENCES projects(organization_id,id)
);

Когда сервер вставляет задачу «Юга» и северный project_id, PostgreSQL ищет пару «Юг плюс этот проект». Такой пары нет, даже если существует строка «Север плюс этот проект». Ожидаемый эффект — отказ внешнего ключа с SQLSTATE 23503. Вставка не должна оставить частичную задачу без правильного родителя.

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

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

Проверка родителя остаётся в API

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

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

Ниже — фрагмент проверки родителя из WorkspaceEndpoints.cs; переменные передаются Database.Session как параметры. После него тот же сеанс выполняет INSERT задачи.

SELECT id FROM projects
WHERE organization_id=@organization
  AND id=@project
  AND deleted_at IS NULL
FOR SHARE;

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

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

Перенос проекта между компаниями

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

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

ON UPDATE CASCADE мог бы автоматически поменять части дочерних ключей, но он не решает вопрос прав на перенос и не объясняет смысл аудита. Автоматическое каскадирование допустимо там, где оно соответствует договорённости модели. В нашем проекте запрет случайной смены компании полезнее скрытого массового перемещения связанных строк.

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

Ограничение не заменяет область чтения

Составной ключ предотвращает неверную связь, но не запрещает запрос SELECT * FROM tasks всем компаниям сразу. Целостность и доступ — разные свойства. API по-прежнему должен передавать доверенную компанию в каждую выборку, а RLS позднее добавит защиту строк на стороне базы.

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

Для ручного наблюдения используйте два INSERT с одинаковым project_id, меняя только компанию задачи. С правильной парой ожидается новая строка, с неверной — отказ и отсутствие строки. Затем создайте проект с таким же названием в другой компании: название не изменит результат проверки, поскольку ключи сравнивают идентификаторы.

Обязательная ссылка не содержит NULL

Обе части внешнего ключа объявлены NOT NULL. Это исключает незаметный обход смысла связи через отсутствующую часть адреса. Если будущая задача может жить без проекта, моделируйте это отдельным допустимым состоянием и разберите поведение составного ключа при NULL. Не делайте поля nullable только для того, чтобы ошибочный INSERT перестал падать. Отсутствие родителя и ссылка на чужого родителя являются разными предметными случаями.

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

Оглавление серии