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)
);
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)
);
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.
Some of the points to consider whether going for a compound primary key or a new surrogate key for the Junction table:
AuthorId
and BookId
as foreign keys). Books
or Authors
tables without first joining to the junction table.The following diagram hopefully makes the case of the compound key clearer (the middle table Nationality
is a junction table of PersonCountry
):
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With