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

1 Answer 1

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),
)
4
  • 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, 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). May 23, 2018 at 13:14
  • Sorry, I meant 3 foreign keys in the table. Would this then require multiple indexes?
    – Kevin
    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". May 25, 2018 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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