0

I have a many-to-many table that contains id's from 2 related tables.

For creating a primary key on this table, is it better to create another integer column and have that as a surrogate PK?

Or should I create the PK on both id columns?

What are the benefits of either method?

Here is the table design with the surrogate key (CodeGroupMappingID)

CREATE TABLE [dbo].[CodeGroupMappings]( [CodeGroupMappingID] [int] IDENTITY(1,1) NOT NULL, [CodeID] [int] NOT NULL, [GroupID] [int] NOT NULL, CONSTRAINT [PK_CodeGroupMappings_CodeGroupMappingID] PRIMARY KEY CLUSTERED ([CodeGroupMappingID] ASC) )
Improve this question
1
  • 4
    Absolutely NOT. This is one place where a surrogate key makes no sense - you're just adding unnecessary complexity. Use your joining fields CodeID and GroupID as the PK! Commented May 22, 2018 at 10:29

1 Answer 1

Reset to default
3

Here's the canonical design for a Many-to-Many linking table. You generally want an index to support traversal in each direction, and (at least for SQL Server) it doesn't matter whether you use a unique constraint, unique index or non-unique non-clustered index for the reverse key index: you end up with the same data structure.

CREATE TABLE CodeGroupMappings ( CodeID int NOT NULL, GroupID int NOT NULL, CONSTRAINT PK_CodeGroupMappings PRIMARY KEY CLUSTERED (CodeID, GroupID), CONSTRAINT AK_CodeGroupMappings UNIQUE (GroupID, CodeID), CONSTRAINT FK_CodeGroupMappings_Code FOREIGN KEY (CodeID) REFERENCES Code(CodeID), CONSTRAINT FK_CodeGroupMappings_Group FOREIGN KEY (GroupID) REFERENCES [Group](GroupID), )
Improve this answer
4
  • What if I have 3 columns in the mappings table? Do I then need to create multiple indexes for ordering each column? Commented May 23, 2018 at 11:14
  • No. Two indexes, one for each Foreign Key. If one FK has two columns, then the index should use the same key order as the referenced PK. If the third column is a non-key column, then leave it out of the indexes (or perhaps add it as an included column in the non-clustered index). Commented May 23, 2018 at 13:14
  • Sorry, I meant 3 foreign keys in the table. Would this then require multiple indexes? Commented May 25, 2018 at 16:05
  • Typically, yes. Each FK would need to be the leading columns in some index. But you can often skip the index for FKs that are just "lookups". Commented May 25, 2018 at 17:01

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.