多对多表应该有主键吗？
如果我有两个具有多对多关系的对象，我通常会在我的数据库模式中使用多对多表对它们进行建模以将两者关联起来。但是，多对多表（或“连接表”）是否应该有自己的主键（整数自动递增）？
例如，我可能有表 A 和 B，每个表都有一个 ID，还有一个名为 A_B 的表，它有一个外键元组 (A_ID, B_ID)。但是A_B是否应该有自己的主键自增ID列呢？
添加它有什么优点和缺点？我个人喜欢多对多连接的自然键。但是主键会带来什么额外的好处呢？
If I have two objects that have a many-to-many relationship, I would typically model them in my database schema with a many-to-many table to relate the two. But should that many-to-many table (or "join table") have a primary key of its own (integer auto-incremented)?
For example, I might have tables A and B, each with an ID, and a table called A_B that has a foreign key tuple of (A_ID, B_ID). But should A_B have a primary key auto-incremented ID column of its own, or not?
What are the advantages and disadvantages of adding it? I personally like natural keys for many-to-many joins. But what added benefit would a primary key add?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我同意奥德所说的一切，除了
在这种情况下，这是一个选择你的毒药，映射表绝对可以是父项，这只是子项是否使用多列 FK 的问题。
以汽车和颜色为例。每年汽车制造 有一定的颜色托盘，每个型号只有有限数量的颜色。 许多 - 许多 :: 汽车型号的颜色
因此，现在设计订单表，其中清楚地存储新车订单的颜色和型号。如果您对每个表创建 FK，则数据库将允许选择不正确的模型/颜色组合（当然您可以使用代码强制执行此操作，但不能以声明方式执行此操作。）。如果是多：多表，您只会获得已指定的组合，
列 FK 并指向基于 ModelID 和 ColorID 构建的 PK，还是想要单列 FK？
那么您是否愿意使用多
编辑
但如果它不是某物的父项，则没有表需要代理键。
I agree with everything Oded said except
In this case it's a pick your poison, the mapping table absolutely can be a parent, it's just a matter of the child using a multicolumn FK or not.
Take a simple case of Car and color. Each Year Auto Makers have a certain pallet of colors and each model only comes in limited number of those colors. Many - Many :: Colors to Cars models
So now design the Order table where new cars orders are stored. Clearly Color and Model will be on the Order table. If you make a FK to each of those tables, the database will permit an incorrect model/color combination to be selected. (Of course you can enforce this with code, you can't do so declaratively.) If you make the parent be the many:many table, you'll only get combinations that have been specified.
SO would you rather have a multicolumn FK and point to a PK built on both ModelID and ColorID or do you want a single column FK?
Pick your poison.
EDIT
But if it's not a parent of something, no table needs a surrogate key.
这样的代理键除了开销之外不会增加任何东西。
使用自然键，如果您关心此表中的重复，请将它们设为复合主键。
扩展一下：
在应用程序中，这个键将毫无意义，将保持不使用状态。
在数据库中，它将没有任何功能，因为您无法在查询中合理地使用它来获取任何类型的有意义的结果。
它也不能合理地用作外键。
Such a surrogate key adds nothing except overhead.
Use the natural keys, make them a composite primary key if you care about duplication in this table.
To expand:
In the application, this key will be meaningless and will remain unused.
In the database, it will have no function, as you can't reasonably use it in a query for any type of meaningful result.
It can't reasonably be used as a foreign key either.
如果跟踪多对多关系的表有自己的主键，并且该键在数据库中的其他任何位置用作外键，那么您将创建对该关系的依赖关系。这种关系永远无法消除。
例如，在汽车颜色示例中，如果汽车颜色曾经停止使用（从多对多关系表中删除），则引用主键的任何表（即购买历史记录）都将被破坏。
If the table tracking the many to many relationship has it's own primary key and that key is used as a foreign key anywhere else in the database then you create a dependency on that relationship. The relationship can never be removed.
For instance in the car color example, if the color for a car is ever discontinued (removed from the many to many relationship table) then any table (i.e. purchase history) referencing the primary key would be broken.
我两种方式都做到了。有时，这对于日后添加功能是有益的。例如，如果曾经有一次，表中的一行包含的内容不仅仅是 2 个 id。如果你不缺空间，我会放一个在那里，因为它不会造成伤害。有时它会干扰 Hibernate 或 ADO.NET 等 ORM 工具，但影响很小。
所以总结一下...
优点
1. 允许潜在的未来增长。
缺点
1. 空间
2.混淆了一些ORM工具。
I have done it both ways. Sometimes it is beneficial for adding a feature down the road. For instance, if there was ever a time that a row in the table would ever contain anything more than just the 2 id's. If you don't lack space I would put one in there just because it can't hurt. Sometimes it can interfere with ORM tools like hibernate or ADO.NET but that is minor.
So to sum it up...
PROS
1. Allows potential future growth.
CONS
1. Space
2. Confuses some ORM tools.
“连接表”一词经常被使用，但我认为我从未见过它被正确定义或解释。我个人避免使用这个术语。据我了解，“连接表”是指任何具有两个外键（或可能超过两个？）的表。
我认为在具有多个外键的表中选择键的标准应该与任何其他表中的选择标准大致相同。问问自己需要强制执行哪些依赖项，哪些是唯一且不可简化的。根据熟悉性、稳定性和简单性的标准选择按键。仅当有充分理由时才添加代理键。
The term "join table" is often used but I don't think I've ever seen it properly defined or explained. Personally I avoid using that term. As I understand it, a "join table" means any table with two foreign keys (or possibly more than two?).
I think the criteria for selecting keys in a table with more than one foreign key should be much the same as in any other table. Ask yourself what dependencies you need to enforce, what is unique and irreducible. Select keys on the criteria of Familiarity, Stability and Simplicity. Add surrogate keys only when you have a good reason to.
它并没有真正提供任何有用的东西。请记住键的用途，即唯一地引用“某物”。像这样的关联表本身并不是“某物”，而是另外两个已经具有键的“某物”的持久性结构。在持久性介质（数据库）之外，它没有任何意义，甚至不应该真正存在或为人所知（例如在业务领域），因此永远（不应该）没有理由通过以下方式引用它：它自己的 ID。
It doesn't really provide anything useful. Keep in mind the purpose of a key, which is to uniquely refer to "something." An association table like this isn't by itself "something" but is rather persistence structure for two other "somethings" which already have keys. Outside of the persistence medium (the database) it doesn't have a meaning and shouldn't even really exist or be known (such as in the business domain), so there would (should) never be a reason to refer to it by its own ID.
有两种情况：
1. 唯一关联
网站有
Users和Roles，每个Role都可以关联到一个Usercode> 仅一次，数据库不会跟踪权限的历史记录。在
UserRole表中，自主 PK 列毫无用处：它不会被其他任何人引用（您将声明/权限与Roles或Users，而不是UserRoles），并且您仍然需要 FKUserRole->User和UserRole->Role。结论：使用元组
(UserId, RoleId)创建 PK 是有意义的，避免了额外的列。2. 重复关联
网站跟踪网球运动员之间的比赛。
Matches表是玩家之间的多对多，但玩家 A 可能与玩家 B 进行过多次比赛。在这种情况下，您无法使用元组
(Player1Id, Player2Id)创建 PK，因为它会以重复的键结尾。Matches表将有一个MatchDate列表示比赛时间，因此您可以在元组(Player1Id, Player2Id, MatchDate)上创建 PK code>，但它开始变得尴尬。结论：在这种情况下，PK 的附加栏对我来说似乎更实用。
There are two cases:
1. Unique associations
A website has
UsersandRoles, everyRolecan be associated to aUserjust once, and the database doesn't keep track of the history of permissions.In the
UserRoletable an autonomous PK column would be just useless: it's not referenced by anybody else (you associate claims/permissions toRolesor toUsers, not toUserRoles) and you still need the FKUserRole->UserandUserRole->Role.Conclusion: it makes sense to create a PK using the tuple
(UserId, RoleId), avoiding extra columns.2. Duplicated associations
A website keeps track of the matches between tennis players.
The
Matchestable is sort of a many-to-many between players, but playerA may have played against playerB multiple times.In this case you can't create a PK using the tuple
(Player1Id, Player2Id), because it would end in duplicated keys.The
Matchestable will have aMatchDatecolumn for the time of the game, therefore you could create a PK on the tuple(Player1Id, Player2Id, MatchDate), but it starts to be awkward.Conclusion: in this case an additional column for the PK seems more practical to me.