如何在SQL Server中建模多对多关系？

Question

我需要在SQL Server数据库中介绍两个表之间的多对多关系，这两个表都有一个整数作为主键。在T-SQL中，最好如何完成此操作？

考虑以下两个示例表定义，它们应该具有多对多关系：

CREATE TABLE [dbo].[Authors] (
    [Id]        INT            IDENTITY (1, 1) NOT NULL,
    CONSTRAINT [PK_Versions] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[Books] (
    [Id]      INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

- aknuds1

Answer 1

传统的方法是使用一个额外的多对多（连接）表，该表链接到两个表：

CREATE TABLE [dbo].[AuthorsBooks] (
    -- Optionally, we can give the table its own surrogate PK
    [Id]      INT IDENTITY(1,1) NOT NULL,
    AuthorId INT NOT NULL,
    BookId INT NOT NULL,

    -- Referential Integrity
    FOREIGN KEY(AuthorId) REFERENCES Authors(Id),
    FOREIGN KEY(BookId) REFERENCES Books(Id),

    -- PK is either the surrogate ...
    PRIMARY KEY CLUSTERED ([Id] ASC)
    -- ... Or the compound key
    -- PRIMARY KEY CLUSTERED (AuthorId, BookId)
);

一项疑点是您是否希望将复合键AuthorId、BookId作为主键，还是是否添加自己的新代理 - 这通常是一种主观偏好。

选择复合主键或新代理主键时需要考虑以下几点：

• 如果没有代理主键，则链接到连接表的外部表需要存储两个复合键（即需要保留AuthorId和BookId作为外键）。
• 因此，新代理提供了主键更窄的潜在好处，这意味着任何链接到该连接表的表都将有一个单一、更窄的外键。
• 然而，使用复合键可以进行优化以使表能够直接连接到底层的Books或Authors表，而无需首先连接到连接表。

下图清晰地说明了复合键的使用情况（中间的Nationality表是PersonCountry的连接表）：

编辑 使用方法很简单 - 如果在多对多的表中存在链接，则认为存在关系。要测试关系是否存在，可以通过连接表进行连接。

-- Find all books written by AuthorId 1234
SELECT b.* 
  FROM Books b 
  INNER JOIN AuthorsBooks ab
     ON b.Id = ab.BookId
  WHERE ab.AuthorId = 1234;

- StuartLC

---

谢谢，我决定采用复合主键的方法，你介意记录一下如何实现吗？ - aknuds1

“FOREIGN KEY（AuthorId）REFERENCES Authors（AuthorId）” 应该改为“FOREIGN KEY（AuthorId）REFERENCES Authors（Id）”，因为你是引用外表的列名。对于BookId的外键也是如此。至少在Visual Studio中编写T-SQL脚本时会出现这个错误。 - aknuds1

@aknuds1 - 是的，你说得完全正确。我们的命名标准是[TableName]Id。 - StuartLC