Create foreign keys to references to other tables and also have matching with mapping table

Question

I'm using SQL Server and setup 4 tables with many references between them

CREATE TABLE [dbo].[Message] 
(
    [Id]             [BIGINT] PRIMARY KEY IDENTITY (1, 1) NOT NULL,
    [ConversationId] [BIGINT] NOT NULL 
        FOREIGN KEY REFERENCES [dbo].[Conversation] ([Id]),
    [UserId]         [BIGINT] NOT NULL 
        FOREIGN KEY REFERENCES [dbo].[User] ([Id])
)

CREATE TABLE [dbo].[Conversation] 
(
    [Id] BIGINT PRIMARY KEY IDENTITY (1, 1) NOT NULL,
    -- other columns
)

CREATE TABLE [dbo].[User] 
(
    [Id] BIGINT PRIMARY KEY IDENTITY (1, 1) NOT NULL,
    -- other columns
);

-- this table is a mapping table mapping Conversation and User table, using pair of composite keys
CREATE TABLE [dbo].[Conversation_Users]   
(
    [ConversationId] BIGINT  NOT NULL,
    [UserId]         BIGINT  NOT NULL,
    -- other columns
    CONSTRAINT [PK__Conversation_Users] 
        PRIMARY KEY (ConversationId, UserId),  -- this defines composite keys
    CONSTRAINT [FK__Conversat_Users_User] 
        FOREIGN KEY (UserId) REFERENCES [User](Id), -- this defines reference to User table
    CONSTRAINT [FK__Conversat_Users_Conv] 
        FOREIGN KEY (ConversationId) REFERENCES [Conversation](Id) -- this defines reference to Conversation table
)

Currently, Message table has 2 difference foreign keys to Conversation and User table. These references also ensure the Message.ConversationId and Message.UserId field must exist in 2 corresponding tables, this works fine.

But I also need these pair of foreign keys must exist in Conversation_Users, too. This doesn't work correctly in my database

How can I setup this mapping correctly?

Answer 1

If you want a key to reference 2 columns, just design it with 2 columns, the syntax accepts a delimited list of columns:

ALTER TABLE dbo.Message ADD CONSTRAINT FK__Conversation_Users_ConvUser FOREIGN KEY (ConversationId, UserId) REFERENCES dbo.Conversation_Users (ConversationId, UserId);

Though you might be better off using a surrogate key on Conversation_Users and then using that:

CREATE TABLE [dbo].[Conversation_Users] ( -- this table is a mapping table mapping Conversation and User table, using pair of composite keys
    ConversationUserID int IDENTITY(1, 1) CONSTRAINT PK__Conversation_Users PRIMARY KEY,
    [ConversationId] BIGINT NOT NULL,
    [UserId] BIGINT NOT NULL,
    -- other columns
    CONSTRAINT [UQ__Conversation_Users] UNIQUE (ConversationId, UserId), -- this defines composite keys
    --CONSTRAINT [FK__Conversat_Users_User] FOREIGN KEY (UserId) REFERENCES [User](Id), -- this defines reference to User table
    CONSTRAINT [FK__Conversat_Users_Conv] FOREIGN KEY (ConversationId) REFERENCES [Conversation] (Id) -- this defines reference to Conversation table
) GO
CREATE TABLE [dbo].[Message] (
    [Id] [BIGINT] PRIMARY KEY IDENTITY(1, 1) NOT NULL,
    ConversationUserID int NOT NULL CONSTRAINT FK__Conversation_Users_ConvUser REFERENCES Conversation_Users (ConversationUserID)
);