应该是结点表从另一个标识表有多个主键吗？ [英] Should junction tables have more than one primary keys from another indentifying table?

问题描述

这是一个例子：原来我有3个表。表B引用了表A.所以现在表B有两个主键。一个用作原始主键，另一个用于强制其与Tabe A的关系。然后，我希望表B与表X具有多对多关系。在添加关系时，MySQL Workbench添加了表Y在表X中同时包含表B的主键和一个主键。因此，表Y现在有三个主键。

Here's an exmaple: Originally I have 3 tables. Table B references Table A. So now Table B has two primary keys. One used as the original primary key and the other one to enforce its relationship with Tabe A. Then I want Table B to have a many-to-many relationship with Table X. As I'm adding the relationship, MySQL Workbench added Table Y with both of Table B primary keys and one primary key in Table X. So Table Y now has three primary keys.

看起来像表B中的第二个主键不需要结表，因为我可以用原始主键标识表B.因此我还需要额外的主键吗？或者我不应该在表A和B之间有不重要的关系？

It seems like the second primary key from Table B in the junction table is unnecessary since I can identify Table B with the original primary key. So do I still need the extra primary key? Or perhaps I should not have an indetifying relationship between Table A and B?

表A和B有一个关系，像用户有很多帖子。帖子必须属于用户。但是Post已经有自己的主键，所以用户需要外键是主键吗？

Table A and B have a relationship something like User has many Post. Post must belong to a User. But Post already has a primary key of its own, so does the foreign key to User need to be a primary key?

EDIT

以下是方案（图表连结如下）。我关注的表是snippet，snippet_topic和tag。从我知道，因为每个片段必须属于一个snippet_topic，它有一个识别关系。所以我在MySQL Workbench中使用了识别关系，并将snippet_topic ID添加为主键。之后，我为标签和代码段添加了m：n关系。 MySQL Workbench将snippet_topic ID添加到联结表（但我已删除它）。我的设计有什么问题吗？

Here's the scenario (diagram link below). The tables I'm focusing on are snippet, snippet_topic and tag. From what I know, since every snippet must belong to a snippet_topic, it has an identifying relationship. So I used the identifying relationship in MySQL Workbench and they addded snippet_topic ID as a primary key. Afterwhich I added a m:n relationship for tag and snippet. MySQL Workbench added snippet_topic ID to the junction table (but I removed it). Is there anything wrong with my design? Or is there a more correct way to this?

图例：
黄色图标 - 主键
红色图标 - 不为空
< img src =https://i.stack.imgur.com/kBVt1.jpgalt =Workbench EER Diagram>

Legend: Yellow icon - primary key Red icon - not null

推荐答案

每个表应该只有一个主键，只有关于这个表。如果您想要在表A中包含找到的表B主键的值的第二列。只需设置第二个索引即可获得性能（如果需要）

each table should only have one primary key which is only about this table. If you then want a second column in Table A containing the values of the table B primary key thats find. Just set up a second index to get performance if requires

这篇关于应该是结点表从另一个标识表有多个主键吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！