联结表是否应具有来自另一个标识表的多个主键? [英] Should junction tables have more than one primary keys from another identifying table?

问题描述

这里是一个例子:最初我有3张桌子.表B引用了表A.因此，表B现在具有两个主键.一个用作原始主键，另一个用作加强其与Tabe A的关系.然后，我希望表B与表X具有多对多关系.在添加关系时，MySQL Workbench添加了表Y具有表B的两个主键和表X的一个主键.因此，表Y现在具有三个主键.

Here's an example: 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 identifying 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?

编辑

这是场景(下面的图表链接).我关注的表是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 added 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?

传奇: 黄色图标-主键 红色图标-不为null

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屋！