Monday, November 25, 2013

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

4 comments:

oakleyses said...

louis vuitton handbags, oakley sunglasses, louboutin, longchamp outlet, nike shoes, louis vuitton outlet stores, chanel handbags, burberry outlet, prada outlet, jordan shoes, tiffany and co, michael kors outlet, tory burch outlet, louis vuitton outlet, longchamp handbags, nike free, true religion jeans, michael kors outlet, kate spade outlet, polo ralph lauren outlet, tiffany and co, prada handbags, polo ralph lauren outlet, michael kors outlet, michael kors outlet, longchamp handbags, oakley sunglasses, ray ban sunglasses, kate spade handbags, burberry outlet, louis vuitton outlet, louboutin outlet, louboutin, coach factory outlet, air max, air max, coach outlet, gucci outlet, christian louboutin shoes, michael kors outlet, coach purses, ray ban sunglasses, michael kors outlet, louis vuitton, coach outlet store online, true religion jeans, oakley sunglasses cheap

oakleyses said...

ralph lauren, lululemon, air max, hollister, north face, nike air max, polo lacoste, vanessa bruno, timberland, vans pas cher, louboutin, louis vuitton, oakley pas cher, air max pas cher, nike roshe run, air max, true religion outlet, barbour, sac longchamp, air force, hollister, sac louis vuitton, nike free, polo ralph lauren, nike trainers, louis vuitton uk, nike roshe, sac hermes, longchamp, michael kors, sac burberry, sac guess, mulberry, new balance pas cher, converse pas cher, sac louis vuitton, hogan outlet, nike tn, north face, true religion outlet, ray ban pas cher, michael kors, air jordan, nike blazer, nike free pas cher, michael kors pas cher, abercrombie and fitch, ray ban sunglasses

oakleyses said...

mac cosmetics, mont blanc, marc jacobs, canada goose outlet, nike huarache, vans shoes, soccer jerseys, hollister, giuseppe zanotti, beats by dre, abercrombie and fitch, longchamp, insanity workout, celine handbags, bottega veneta, ghd, nfl jerseys, north face outlet, chi flat iron, ugg boots, birkin bag, ugg australia, canada goose, herve leger, ugg pas cher, rolex watches, valentino shoes, canada goose uk, canada goose, ferragamo shoes, canada goose, ugg boots, uggs outlet, north face jackets, soccer shoes, asics running shoes, new balance shoes, p90x, lululemon outlet, canada goose jackets, mcm handbags, instyler, babyliss pro, ugg, wedding dresses, jimmy choo outlet, reebok outlet, nike roshe run

oakleyses said...

parajumpers, karen millen, air max, converse, pandora charms, moncler, louboutin, moncler, links of london, lancel, juicy couture outlet, oakley, hollister, pandora charms, supra shoes, thomas sabo, canada goose, gucci, wedding dresses, timberland boots, swarovski crystal, air max, coach outlet store online, moncler, ray ban, canada goose, moncler, ugg, louis vuitton, swarovski, hollister, montre homme, moncler, hollister clothing store, ralph lauren, rolex watches, moncler outlet, moncler, iphone 6 cases, baseball bats, juicy couture outlet, toms shoes, vans, pandora jewelry, ugg, converse shoes