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

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

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

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

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

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

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

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

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

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

CREATE TABLE user_roles (
    user_id BIGINT NOT NULL,
    role_id BIGINT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

Здесь составной первичный ключ одновременно задаёт уникальность пары и делает оба столбца обязательными. Эквивалентная модель возможна с отдельным искусственным первичным ключом и ограничением UNIQUE (user_id, role_id).

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

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

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

Сервис назначения ролей повторяет запрос после сетевого тайм-аута. Без ограничения уникальности создаются две одинаковые строки: пользователь видит одну роль, но аудит и агрегирующие запросы учитывают две записи.

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

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

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

Почему двух отдельных ограничений UNIQUE недостаточно?

Ограничения UNIQUE по user_id и по role_id запретили бы пользователю иметь несколько ролей и роли принадлежать нескольким пользователям. Для связи «многие ко многим» нужна уникальность комбинации, а не каждого участника отдельно.
Можно ли полагаться только на проверку в приложении?

Нет. Два параллельных запроса могут одновременно не найти существующую строку и оба попытаться вставить новую. Уникальное ограничение проверяется самой базой в рамках конкурентного доступа и является окончательной защитой от дублирования.
Зачем иногда выбирать отдельный первичный ключ вместе с составным UNIQUE?

Отдельный идентификатор может упростить внешние ссылки, маршрутизацию API и работу ORM. При этом UNIQUE на (user_id, role_id) всё равно необходимо, иначе искусственный первичный ключ разрешит несколько строк для одной бизнес-связи.