Enforcing two M2M relationships with FK with SQL Server

I have the following entities


**Account**
Id
Name


**User**
Id
Name

Users can belong to many accounts.

**AccountUser**
Id
Account_Id FK
User_Id Fk

Accounts can have many user groups.

**AccountUserGroup**
Id
Account_Id FK
Name

Users can be a member of an account's user group.

**AccountUserGroupMembership**
Id
User_Id FK
AccountUserGroup FK

How could I enforce that only users who belong to a AccountUserGroup be added to AccountUserGroupMembership for those accounts without business logic. Is this possible in SQL Server?

For example

User 1 belong to Account 100. Account 200 has a AccountUserGroup, Id 1000. AccountUserGroupMembership of User Id 1 AccountUserGroupId 200 would not be valid.

Thank you

I have FK constraints on all my tables so far

AccountUser - AccId and UserId AccountUserGroup - AccId AccountUserGroupMembership - AccUserGroupId and AccountUserId

Solution

Firstly, many-many join tables do not normally need a separate ID column. The primary key should just be a combination of the two foreign keys.

In answer to your question: you need to make AccountUserGroupMembership have a foreign key against AccountUserGroup, not against User or Account. And AccountUserGroup itself needs to have a key (can be a secondary unique key) containing the AccountId so that it can pass through.

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

CREATE TABLE 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)  -- extra key needed to apply the foreign key to it
);

CREATE TABLE AccountUserGroupMembership (
  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)
);

This means that you can then only insert an AccountUserGroupMembership row where the pair of AccountUserGroup_Id, Account_Id is present in the AccountUserGroup table.