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

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

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

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

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

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

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

Пусть в таблице родителей номер объекта уникален только внутри клиента. Если дочерняя таблица хранит client_id и parent_id, но внешний ключ проверяет только parent_id, возможна ошибочная ссылка: строка клиента A будет связана с объектом клиента B.

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

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

Родительская таблица должна иметь ключ client_id, parent_id. Дочерняя таблица хранит оба значения и объявляет внешний ключ на ту же пару:

CREATE TABLE customer_order (
    client_id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    PRIMARY KEY (client_id, order_id)
);

CREATE TABLE order_item (
    client_id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    item_no INTEGER NOT NULL,
    PRIMARY KEY (client_id, order_id, item_no),
    FOREIGN KEY (client_id, order_id)
        REFERENCES customer_order (client_id, order_id)
);

Проверка выполняется позиционно: первый столбец дочернего ключа сопоставляется с первым столбцом родительского, второй — со вторым. Поэтому порядок столбцов должен совпадать, а родительская пара должна быть покрыта PRIMARY KEY или подходящим UNIQUE-ограничением.

Если каждый дочерний объект обязан принадлежать клиенту, оба столбца внешнего ключа объявляют NOT NULL. Иначе обычные правила работы с NULL могут позволить строку с отсутствующей ссылкой; для необязательной составной ссылки дополнительно рассматривают MATCH FULL, если пара должна быть либо полностью заполнена, либо полностью пуста.

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

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

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

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

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

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

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

1. Достаточно ли объявить составной внешний ключ, если в родительской таблице нет уникальности по этой паре?

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

2. Что изменится, если client_id в дочерней таблице оставить допускающим NULL?

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

Если ссылка необязательна, нужно явно выбрать семантику: обычно оба столбца делают nullable и не допускают частично заполненную пару через MATCH FULL, если это поддерживается выбранной СУБД. Если принадлежность обязательна, надёжнее объявить оба столбца NOT NULL.

3. Нужен ли индекс на дочерние столбцы составного внешнего ключа?

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

Обычно создают индекс с ведущими столбцами (client_id, parent_id), совпадающими с внешним ключом. Это ускоряет проверки и операции по конкретному клиенту, но увеличивает объём хранения и стоимость вставок или обновлений.