Реализация двух отношений M2M с использованием внешних ключей в SQL Server

Содержание

Вопрос или проблема
Ответ или решение
Шаг 1: Определение таблиц
Шаг 2: Настройка ограничений
Шаг 3: Создание триггера
Заключение

Вопрос или проблема

У меня есть следующие сущности


**Аккаунт**
Id
Имя


**Пользователь**
Id
Имя

Пользователи могут принадлежать к нескольким аккаунтам.

**АккаунтПользователь**
Id
Account_Id FK
User_Id Fk

Аккаунты могут иметь множество групп пользователей.

**АккаунтГруппаПользователей**
Id
Account_Id FK
Имя

Пользователи могут быть членами группы пользователей аккаунта.

**ЧленствоВГруппеПользователейАккаунта**
Id
User_Id FK
AccountUserGroup FK

Как я могу обеспечить, чтобы только пользователи, принадлежащие к группе пользователей аккаунта, могли быть добавлены в ЧленствоВГруппеПользователейАккаунта для этих аккаунтов без бизнес-логики. Это возможно в SQL Server?

Например

Пользователь 1 принадлежит аккаунту 100.
Аккаунт 200 имеет ГруппуПользователейАккаунта, Id 1000.
ЧленствоПользователя с User Id 1 и AccountUserGroupId 200 не будет действительным.

Спасибо

У меня есть ограничения FK на всех моих таблицах так far

AccountUser – AccId и UserId
AccountUserGroup – AccId
ЧленствоВГруппеПользователейАккаунта – AccUserGroupId и AccountUserId

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

В ответ на ваш вопрос: вам нужно сделать так, чтобы ЧленствоВГруппеПользователейАккаунта имело внешний ключ, указывающий на АккаунтГруппаПользователей, а не на Пользователя или Аккаунт. И АккаунтГруппаПользователей сама должна иметь ключ (может быть вторичным уникальным ключом), содержащим AccountId, чтобы он мог пройти через.

AccountUser (
  User_Id int NOT NULL REFERENCES [User] (Id),
  Account_Id int NOT NULL REFERENCES Account (Id),
  PRIMARY KEY (User_Id, Account_Id)
)

AccountUserGroup (
  Id int NOT NULL IDENTITY PRIMARY KEY,
  Account_Id int NOT NULL REFERENCES Account (Id),
  Name varchar(50) NOT NULL UNIQUE,
  UNIQUE (Account_Id, Id)
)

ЧленствоВГруппеПользователейАккаунта (
  AccountUserGroup_Id int NOT NULL,
  Account_Id int NOT NULL,
  User_Id int NOT NULL REFERENCES [User] (Id),
  PRIMARY KEY (Account_Id, AccountUserGroup_Id, User_Id),
  FOREIGN KEY (AccountUserGroup_Id, Account_Id) REFERENCES AccountUserGroup (Account_Id, Id)
)

Это означает, что вы можете вставить строку ЧленствоВГруппеПользователейАккаунта только тогда, когда пара AccountUserGroup_Id, Account_Id присутствует в таблице АккаунтГруппаПользователей.

Ответ или решение

Для обеспечения корректной работы системы с учетом описанных вами отношений «многие-ко-многим» в SQL Server необходимо правильно настроить структуру таблиц и ограничения внешних ключей. Эта структура должна позволить добавлять пользователей в группы пользователей аккаунта только в том случае, если они действительно принадлежат к этому аккаунту.

Шаг 1: Определение таблиц

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

CREATE TABLE Account (
    Id INT NOT NULL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);

CREATE TABLE [User] (
    Id INT NOT NULL PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
);

CREATE TABLE AccountUser (
    User_Id INT NOT NULL,
    Account_Id INT NOT NULL,
    PRIMARY KEY (User_Id, Account_Id),
    FOREIGN KEY (User_Id) REFERENCES [User] (Id),
    FOREIGN KEY (Account_Id) REFERENCES Account (Id)
);

CREATE TABLE AccountUserGroup (
    Id INT NOT NULL IDENTITY PRIMARY KEY,
    Account_Id INT NOT NULL,
    Name VARCHAR(50) NOT NULL UNIQUE,
    FOREIGN KEY (Account_Id) REFERENCES Account (Id),
    UNIQUE (Account_Id, Id)
);

CREATE TABLE AccountUserGroupMembership (
    AccountUserGroup_Id INT NOT NULL,
    User_Id INT NOT NULL,
    PRIMARY KEY (AccountUserGroup_Id, User_Id),
    FOREIGN KEY (User_Id) REFERENCES [User] (Id),
    FOREIGN KEY (AccountUserGroup_Id, Account_Id) REFERENCES AccountUserGroup (Id, Account_Id)
);

Шаг 2: Настройка ограничений

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

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

Шаг 3: Создание триггера

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

CREATE TRIGGER trg_CheckUserInAccountUserGroup
ON AccountUserGroupMembership
INSTEAD OF INSERT
AS
BEGIN
    DECLARE @AccountUserGroup_Id INT, @User_Id INT;

    SELECT @AccountUserGroup_Id = AccountUserGroup_Id, @User_Id = User_Id
    FROM inserted;

    IF EXISTS (
        SELECT 1
        FROM AccountUser AU
        JOIN AccountUserGroup AUG ON AU.Account_Id = AUG.Account_Id
        WHERE AU.User_Id = @User_Id AND AUG.Id = @AccountUserGroup_Id
    )
    BEGIN
        -- Если пользователь принадлежит к аккаунту, выполняем вставку
        INSERT INTO AccountUserGroupMembership (AccountUserGroup_Id, User_Id)
        VALUES (@AccountUserGroup_Id, @User_Id);
    END
    ELSE
    BEGIN
        RAISERROR ('Пользователь не принадлежит к данному аккаунту для указанной группы пользователей.', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;

Заключение

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