MS SQL 使用联结表创建多对多关系
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14676342/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MS SQL creating many-to-many relation with a junction table
提问by sp00ctbr
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_IDmy 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] ?
[并且只保留它没有主键或身份表]？
回答by Taryn
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 Movieand Categorytable.
主键是两列的组合。您还将有一个从每列到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.
Using these two fields as the PRIMARY KEYwill prevent duplicate movie/category combinations from being added to the table.
使用这两个字段作为PRIMARY KEY将防止将重复的电影/类别组合添加到表中。
回答by Marlin Pierce
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.
但是，对于像你正在做的事情，你可能只用这两个字段就可以了。我仍然建议将这两个字段设为复合主键，或者至少在这两个字段上放置一个唯一索引。
回答by higgs_boson
I would go with the 2nd junction table. But make those two fields as Primary key. That will restrict duplicate entries.
我会选择第二个连接桌。但是将这两个字段作为主键。这将限制重复条目。
