SQL Junction Tables

Written 11/2/2017

Many to many relationship

Junction tables can be used if you want to create a many-to-many relationships in a SQL database. Let us imagine the model we would use between a book and an author. One book can have more than one author, and one author can be the author of more than one book. That is a many to many relationship.

Avoid duplicate entries

We do not want duplicate entries in our database tables.
If we only created a table for books and one for authors, we would end up with duplicate entries, as soon as one book has more than one author, or if one author writes more than one book.
A junction table can help us to create the many-to-many relationship. By the use of a junction table we avoid duplicate entries.

sql junction table

Creating the tables

First let us create the two main tables for books and authors:

CREATE TABLE Authors
(
AuthorID int IDENTITY(1,1) PRIMARY KEY,
AuthorName nvarchar(50) NOT NULL
)
CREATE TABLE Books
(
BookID int IDENTITY(1,1) PRIMARY KEY,
BookTitle nvarchar(50) NOT NULL
)

We now need to create the junction table, that will link the Authors and the Books tables together. The junction table is created by using the primary key from the Author and Book tables.

CREATE TABLE AuthorBook
(
AuthorID int NOT NULL,
BookID int NOT NULL,
CONSTRAINT PK_AuthorBook PRIMARY KEY
(
AuthorID,
BookID
),
FOREIGN KEY (AuthorID) REFERENCES Authors (AuthorID),
FOREIGN KEY (BookID) REFERENCES Books (BookID)
)

We have now created a table with columns for the AuthorID and the BookID. This table also uses a combination of these two columns as the primary key. This means that each author-book pair is unique. Each author can belong to many books, each book can belong to many authors. But each pair can only occur once.

Note that the columns in the junction table are setup as foreign keys to the Authors and Books tables. This is important as it keeps us from adding authors to a book that doesn’t exist or deleting a book from the database if there are still authors belonging to it.

Adding data

Let us try to add a single book with two authors. Please execute the following queries:

insert into Books (BookTitle)
values ('Beginning T-SQL')
insert into Authors (AuthorName)
values ('Kathi Kellenberger')
insert into Authors (AuthorName)
values ('Scott Shaw')
insert into AuthorBook (AuthorID, BookID)
values (1, 1)
insert into AuthorBook (AuthorID, BookID)
values (2, 1)

Pulling data

If I want to see which authors have written the book with the book title: "Beginning T-SQL", I can execute the following query:

select AuthorName 
from Books
left join AuthorBook
on AuthorBook.BookID = Books.BookID
left join Authors
on Authors.AuthorID = AuthorBook.AuthorID
where BookTitle = 'Beginning T-SQL'

Here you see the result:

sql select statement

If I want to see which authors have written the book with the bookID = 1, I can execute the following query:

select AuthorName 
from Books
left join AuthorBook
on AuthorBook.BookID = Books.BookID
left join Authors
on Authors.AuthorID = AuthorBook.AuthorID
where BookID = 1