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)
  • 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! – Vérace May 22 '18 at 10:29

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 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 | |
  • What if I have 3 columns in the mappings table? Do I then need to create multiple indexes for ordering each column? – Kevin May 23 '18 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). – David Browne - Microsoft May 23 '18 at 13:14
  • Sorry, I meant 3 foreign keys in the table. Would this then require multiple indexes? – Kevin May 25 '18 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". – David Browne - Microsoft May 25 '18 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.