多对多表中的一个或两个主键？ [英] One or Two Primary Keys in Many-to-Many Table?
问题描述
我在我的数据库中有以下具有多对多关系的表，它由具有每个主表的主键的外键的连接表表示：
I have the following tables in my database that have a many-to-many relationship, which is expressed by a connecting table that has foreign keys to the primary keys of each of the main tables:
- Widget：WidgetID（PK），标题，价格
- User：UserID（PK），FirstName，LastName
假设每个User-Widget组合是唯一的。我可以看到两个选项如何结构连接表定义数据关系：
Assume that each User-Widget combination is unique. I can see two options for how to structure the connecting table that defines the data relationship:
- UserWidgets1：UserWidgetID（PK），WidgetID ），UserID（FK）
- UserWidgets2：WidgetID（PK，FK），UserID（PK，FK）
选项1有一个主键的列。然而，这似乎是不必要的，因为存储在表中的唯一数据是两个主表之间的关系，并且该关系本身可以形成唯一键。因此，导致选项2，其具有两列主键，但丢失选项1具有的单列唯一标识符。我也可以选择在第一个表中添加两列唯一索引（WidgetID，UserID）。
Option 1 has a single column for the Primary Key. However, this seems unnecessary since the only data being stored in the table is the relationship between the two primary tables, and this relationship itself can form a unique key. Thus leading to option 2, which has a two-column primary key, but loses the one-column unique identifier that option 1 has. I could also optionally add a two-column unique index (WidgetID, UserID) to the first table.
两个性能之间有任何真正的区别是否更倾向于使用一种方法来构造UserWidgets多对多表？
Is there any real difference between the two performance-wise, or any reason to prefer one approach over the other for structuring the UserWidgets many-to-many table?
推荐答案
您只有一个主键在任一情况下。第二个是所谓的复合键。没有好的理由引入一个新的列。实际上，您必须在所有候选键上保留唯一索引。添加新列只需要维护费用。
You only have one primary key in either case. The second one is what's called a compound key. There's no good reason for introducing a new column. In practise, you will have to keep a unique index on all candidate keys. Adding a new column buys you nothing but maintenance overhead.
使用选项2。
这篇关于多对多表中的一个或两个主键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
