MS SQL creating many-to-many relation with a junction table

junction-table, many-to-many, sql, sql-server

Solution

I would use the second junction table:

MOVIE_CATEGORY_JUNCTION
Movie_ID
Category_ID

The primary key would be the combination of both columns. You would also have a foreign key from each column to the `Movie` and `Category` table.

The junction table would look similar to this:

create table movie_category_junction
(
  movie_id int,
  category_id int,
  CONSTRAINT movie_cat_pk PRIMARY KEY (movie_id, category_id),
  CONSTRAINT FK_movie 
      FOREIGN KEY (movie_id) REFERENCES movie (movie_id),
  CONSTRAINT FK_category 
      FOREIGN KEY (category_id) REFERENCES category (category_id)
);

See SQL Fiddle with Demo.

Using these two fields as the `PRIMARY KEY` will prevent duplicate movie/category combinations from being added to the table.

Problem

I'm using Microsoft SQL Server Management Studio and while creating a junction table should I create an ID column for the junction table, if so should I also make it the primary key and identity column? Or just keep 2 columns for the tables I'm joining in the many-to-many relation? For example if this would be the many-to many tables: ``` MOVIE Movie_ID Name etc... CATEGORY Category_ID Name etc... ``` Should I make the junction table: ``` MOVIE_CATEGORY_JUNCTION Movie_ID Category_ID Movie_Category_Junction_ID ``` [and make the `Movie_Category_Junction_ID` my Primary Key and use it as the Identity Column] ? Or: ``` MOVIE_CATEGORY_JUNCTION Movie_ID Category_ID ``` [and just leave it at that with no primary key or identity table] ?

Original source