How do I model a many-to-many relation in SQL Serv

问题:

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

回答1:

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

CREATE TABLE [dbo].[AuthorsBooks] (
    -- Can have your own surrogate if you want
    [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 can be own surrogate, or the composite (AuthorId, BookId)
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

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

Edit

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