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

Question

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] ?

Answer 1

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.