如何在 SQL Server 中为多对多关系建模? [英] How do I model a many-to-many relation in SQL Server?

问题描述

我需要在 SQL Server 数据库中的两个表之间引入多对多关系，这两个表都有一个整数作为主键.这在 T-SQL 中如何最好地完成?

I need to introduce a many-to-many relationship between two tables, which both have an integer for primary key, in a SQL Server database. How is this best done in T-SQL?

考虑以下两个应该存在多对多关系的示例表定义:

Consider the following two example table definitions for which there should be a many-to-many relationship:

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)
);

推荐答案

传统的方式是使用一个额外的many:many(连接)表，它链接到两个表:

The traditional way is to use an additional many:many (junction) table, which links to both tables:

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 作为主键，或者是否添加您自己的新代理 - 这通常是一种主观偏好.

One moot point is whether you want the compound key AuthorId, BookId to be the Primary Key, or whether to add your own new Surrogate - this is usually a subjective preference.

要考虑为 Junction 表使用复合主键还是新代理键的一些要点:

Some of the points to consider whether going for a compound primary key or a new surrogate key for the Junction table:

• 如果没有代理，链接到联结表的外部表将需要存储两个复合键(即需要同时保留 AuthorId 和 BookId 作为外键).
• 因此，新代理提供了更窄主键的潜在好处，这意味着链接到此联结表的任何表都将有一个更窄的外键.
• 但是，使用复合键可以带来优化优势，即表可以直接连接到基础 Books 或 Authors 表，而无需先连接到连接表.

• Without the surrogate, external tables linking to the junction table would need to store both compound keys (i.e. would need to retain both AuthorId and BookId as foreign keys).
• So a new surrogate offers the potential benefit of a narrower primary key, which then means any tables linking to this junction table will have a single, narrower foreign key.
• However, with the compound keys, there can be an optimisation benefit that tables can join directly to the underlying Books or Authors tables without first joining to the junction table.

下图希望能让复合键的大小写更清晰(中间表Nationality是PersonCountry的连接表):

The following diagram hopefully makes the case of the compound key clearer (the middle table Nationality is a junction table of PersonCountry):

编辑

用法很简单 - 如果链接存在于 many:many 表中，则认为该关系存在.要测试是否存在，您可以通过链接表加入"，例如

Usage is straightforward - if the link exists in the many:many table, then the relationship is deemed to exist. To test the existence, you 'join through' the link table e.g.

-- 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;

这篇关于如何在 SQL Server 中为多对多关系建模?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！