MS SQL与联结表创建多对多关系 [英] MS SQL creating many-to-many relation with a junction table

问题描述

我正在使用Microsoft SQL Server Management Studio，并且在创建联结表时是否应为联结表创建ID列，如果可以，还应将其设为主键和标识列吗?还是为我要加入的多对多关系表保留2列?

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...

我应该创建连接表:

MOVIE_CATEGORY_JUNCTION
Movie_ID
Category_ID
Movie_Category_Junction_ID

[并将Movie_Category_Junction_ID作为我的主键，并将其用作标识列]?

[and make the Movie_Category_Junction_ID my Primary Key and use it as the Identity Column] ?

或者:

MOVIE_CATEGORY_JUNCTION
Movie_ID
Category_ID

[并且只保留它而没有主键或标识表]?

[and just leave it at that with no primary key or identity table] ?

推荐答案

我将使用第二个联结表:

I would use the second junction table:

MOVIE_CATEGORY_JUNCTION
Movie_ID
Category_ID

主键将是两列的组合.您还将在每一列中都有一个外键，分别指向Movie和Category表.

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.

联结表看起来类似于:

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)
);

请参见带演示的SQL提琴.

将这两个字段用作PRIMARY KEY将防止将重复的影片/类别组合添加到表中.

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

这篇关于MS SQL与联结表创建多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！