Understanding a SQL Junction Table

My blog is now located at http://www.WhoIsSethLong.com

I was recently asked about a sql junction table and thought I’d share my thoughts about them with the few people who read this.  First off, I am NOT a DBA.  So, if something is not correct or accurate please feel free to correct me.

Junction tables are used when dealing with many-to-many relationships in a SQL database.  If you’re wondering what exactly a many-to-many relationship is, let me try to briefly explain.  Suppose we are working at a school and have a table full of student names and another table full of classrooms.  Each of the students can belong to multiple classrooms or none at all.  Likewise, each classroom can have multiple students or none at all.  This is an example of a many-to-many relationship.

A junction table will allow us to create the many-to-many relationship and most importantly, let us keep from adding duplicate entries as you’ll soon see.

To start, lets create a student table and a classroom table.

CREATE TABLE Students
(
    StudentID int IDENTITY(1,1) PRIMARY KEY,
    StudentName nchar(50) NOT NULL
)

CREATE TABLE Classrooms
(
    ClassroomID int IDENTITY(1,1) PRIMARY KEY,
    RoomNumber int NOT NULL
)

Now that we have our two tables created we need to create the junction table that will link them together.  The junction table is created by using the primary key from the Classrooms and Students tables.

CREATE TABLE StudentClassroom
(
    StudentID int NOT NULL,
    ClassroomID int NOT NULL,
    CONSTRAINT PK_StudentClassroom PRIMARY KEY
    (
        StudentID,
        ClassroomID
    ),
    FOREIGN KEY (StudentID) REFERENCES Students (StudentID),
    FOREIGN KEY (ClassroomID) REFERENCES Classrooms (ClassroomID)
)

We have now created a table with columns for the StudentID and the ClassroomID.  This table also uses a combination of these two columns as the primary key.  This means that each student-classroom pair is unique.  Each student can belong to many classrooms, each classroom can belong to many students but each pair can only occur once.

You should also note that the columns in the junction table are setup as foreign keys to the Students and Classrooms tables.  This is important as it keeps us from adding students to a classroom that doesn’t exist or deleting a classroom from the database if there are still students belonging to it.

To see what students belong to what classrooms we can use the junction table and the following query:

SELECT StudentName, RoomNumber
FROM StudentClassroom
JOIN Students ON Students.StudentID = StudentClassroom.StudentID
JOIN Classrooms ON Classrooms.ClassroomID = StudentClassroom.ClassroomID

So, that’s a junction table in a nut shell.

- Seth Long