更新时间:2023-02-15 18:34:16
传统的方式是使用一个额外的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
表,而无需先连接到连接表.AuthorId
and BookId
as foreign keys). 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;