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?
我正在使用Microsoft SQL Server Management Studio，在创建联结表时，我应该为联结表创建一个ID列，如果是这样，我还应该将其作为主键和标识列吗？或者只是为我加入多对多关系的表保留2列？
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] ?
[并将Movie_Category_Junction_ID设为我的主键并将其用作标识列]？
Or:
要么：
MOVIE_CATEGORY_JUNCTION
Movie_ID
Category_ID
[and just leave it at that with no primary key or identity table] ?
[并且只留下没有主键或身份表的那个]？
3 个解决方案
#1
41
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.
主键是两列的组合。您还可以从每列到Movie和Category表中有一个外键。
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.
请参阅SQL Fiddle with Demo。
Using these two fields as the PRIMARY KEY will prevent duplicate movie/category combinations from being added to the table.
将这两个字段用作PRIMARY KEY将防止将重复的电影/类别组合添加到表中。
#2
16
There are different schools of thought on this. One school prefers including a primary key and naming the linking table something more significant than just the two tables it is linking. The reasoning is that although the table may start out seeming like just a linking table, it may become its own table with significant data.
对此有不同的思想流派。一所学校更喜欢包括一个主键，并将链接表命名为比它所链接的两个表更重要的东西。原因是虽然表可能看起来只是一个链接表，但它可能会成为拥有重要数据的表。
An example is a many-to-many between magazines and subscribers. Really that link is a subscription with its own attributes, like expiration date, payment status, etc.
一个例子是杂志和订阅者之间的多对多。实际上，该链接是具有自己属性的订阅，例如到期日期，付款状态等。
However, I think sometimes a linking table is just a linking table. The many to many relationship with categories is a good example of this.
但是，我认为有时链接表只是一个链接表。与类别的多对多关系就是一个很好的例子。
So in this case, a separate one field primary key is not necessary. You could have a auto-assign key, which wouldn't hurt anything, and would make deleting specific records easier. It might be good as a general practice, so if the table later develops into a significant table with its own significant data (as subscriptions) it will already have an auto-assign primary key.
因此，在这种情况下，不需要单独的一个字段主键。你可以有一个自动分配键，这不会伤害任何东西，并会更容易删除特定的记录。它可能是一般的做法，所以如果表后来发展成具有其自身重要数据（作为订阅）的重要表，则它将具有自动分配主键。
You can put a unique index on the two fields to avoid duplicates. This will even prevent duplicates if you have a separate auto-assign key. You could use both fields as your primary key (which is also a unique index).
您可以在两个字段上放置唯一索引以避免重复。如果您有一个单独的自动分配键，这甚至可以防止重复。您可以将这两个字段用作主键（也是唯一索引）。
So, the one school of thought can stick with integer auto-assign primary keys, and avoids compound primary keys. This is not the only way to do it, and maybe not the best, but it won't lead you wrong, into a problem where you really regret it.
因此，一种思想可以坚持使用整数自动分配主键，并避免使用复合主键。这不是唯一的方法，也许不是最好的方法，但它不会导致你的错误，成为你真正后悔的问题。
But, for something like what you are doing, you will probably be fine with just the two fields. I'd still recommend either making the two fields a compound primary key, or at least putting a unique index on the two fields.
但是，对于你正在做的事情，你可能只对这两个领域很好。我仍然建议将两个字段作为复合主键，或者至少在两个字段上放置一个唯一索引。
#3
0
I would go with the 2nd junction table. But make those two fields as Primary key. That will restrict duplicate entries.
我会选择第二个交汇处。但是将这两个字段作为主键。这将限制重复的条目。
#1
41
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.
主键是两列的组合。您还可以从每列到Movie和Category表中有一个外键。
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.
请参阅SQL Fiddle with Demo。
Using these two fields as the PRIMARY KEY will prevent duplicate movie/category combinations from being added to the table.
将这两个字段用作PRIMARY KEY将防止将重复的电影/类别组合添加到表中。
#2
16
There are different schools of thought on this. One school prefers including a primary key and naming the linking table something more significant than just the two tables it is linking. The reasoning is that although the table may start out seeming like just a linking table, it may become its own table with significant data.
对此有不同的思想流派。一所学校更喜欢包括一个主键，并将链接表命名为比它所链接的两个表更重要的东西。原因是虽然表可能看起来只是一个链接表，但它可能会成为拥有重要数据的表。
An example is a many-to-many between magazines and subscribers. Really that link is a subscription with its own attributes, like expiration date, payment status, etc.
一个例子是杂志和订阅者之间的多对多。实际上，该链接是具有自己属性的订阅，例如到期日期，付款状态等。
However, I think sometimes a linking table is just a linking table. The many to many relationship with categories is a good example of this.
但是，我认为有时链接表只是一个链接表。与类别的多对多关系就是一个很好的例子。
So in this case, a separate one field primary key is not necessary. You could have a auto-assign key, which wouldn't hurt anything, and would make deleting specific records easier. It might be good as a general practice, so if the table later develops into a significant table with its own significant data (as subscriptions) it will already have an auto-assign primary key.
因此，在这种情况下，不需要单独的一个字段主键。你可以有一个自动分配键，这不会伤害任何东西，并会更容易删除特定的记录。它可能是一般的做法，所以如果表后来发展成具有其自身重要数据（作为订阅）的重要表，则它将具有自动分配主键。
You can put a unique index on the two fields to avoid duplicates. This will even prevent duplicates if you have a separate auto-assign key. You could use both fields as your primary key (which is also a unique index).
您可以在两个字段上放置唯一索引以避免重复。如果您有一个单独的自动分配键，这甚至可以防止重复。您可以将这两个字段用作主键（也是唯一索引）。
So, the one school of thought can stick with integer auto-assign primary keys, and avoids compound primary keys. This is not the only way to do it, and maybe not the best, but it won't lead you wrong, into a problem where you really regret it.
因此，一种思想可以坚持使用整数自动分配主键，并避免使用复合主键。这不是唯一的方法，也许不是最好的方法，但它不会导致你的错误，成为你真正后悔的问题。
But, for something like what you are doing, you will probably be fine with just the two fields. I'd still recommend either making the two fields a compound primary key, or at least putting a unique index on the two fields.
但是，对于你正在做的事情，你可能只对这两个领域很好。我仍然建议将两个字段作为复合主键，或者至少在两个字段上放置一个唯一索引。
#3
0
I would go with the 2nd junction table. But make those two fields as Primary key. That will restrict duplicate entries.
我会选择第二个交汇处。但是将这两个字段作为主键。这将限制重复的条目。