是否具有N-M关系的复合主键? [英] Composite primary keys in N-M relation or not?

问题描述

让我们说我们有3个表(实际上我现在有2个表，但是这个示例可能会更好地说明这种想法):

Lets say we have 3 tables (actually I have 2 at the moment, but this example might illustrate the thought better):

[人员]

• ID:int，主键
• 名称:nvarchar(xx)

[组]

• ID:int，主键
• 名称:nvarchar(xx)

[角色]

• ID:int，主键
• 名称:nvarchar(xx)

[PersonGroupRole]

• Person_ID:int， 主要组合?
• Group_ID:int， 是否为主要组合?
• 角色ID:int， 主要组合?

关系 PersonGroupRole 中的3个ID中的任何一个是否应标记为PRIMARY键，还是应将其全部3个ID组合为一个组合?做或不做的真正好处是什么?

Should any of the 3 ID's in the relation PersonGroupRole be marked as PRIMARY key or should they all 3 be combined into one composite?? whats the real benefit of doing it or not?

据我所知，我仍然可以加入，所以Person JOIN PersonGroupRole JOIN Group会告诉我哪些人属于哪个Group等.

I can join anyways as far as I know, so Person JOIN PersonGroupRole JOIN Group gives me which persons are in which Groups etc.

我将在SQL-express和SQL-server之上使用LINQ/C#/.NET，因此，如果有任何有关语言/SQL的原因可能使选择更加明确，那就是我要问的平台.

I will be using LINQ/C#/.NET on top of SQL-express and SQL-server, so if there is any reasons regarding language/SQL that might make the choice more clear, that's the platform I ask about.

期待看到弹出的答案，因为在制作组合键时，我已经多次想到这些主键/索引.

Looking forward to see what answers pops up, as I have thought of these primary keys/indexes many times when making combined ones.

好吧，我现在能看到的问题是被误解了.

Okay, the question was to be misunderstood I can see now.

问题在于，是否有必要将PersonGroupRole中的三个ID标记为用于索引目的的PRIMARY KEYS.这会增加与三个表中的每个表的联接的额外速度，还是应该使它们在PersonGroupRole表中没有PRIMARY KEY而只在单独的表中成为Primary.

The question is about, if it makes any sense to mark the three ID's in PersonGroupRole as PRIMARY KEYS for index purpose. Will this add extra speed for joining with each of the three tables, or should they stay without PRIMARY KEY in the PersonGroupRole table and only be Primary in the separate tables.

对不起，关于混乱.会尝试更好地解释我的问题.

Sorry, about the confusion. Will try to explain my questions better.

推荐答案

复合键可以清楚地标识每一行(假设某人不能在同一组中两次扮演相同的角色)，因此它是一个很好的主键键.

The composite key distinctly identifies each row (assuming that a person can't have the same role in the same group twice), so it makes for a good primary key.

您是否实际上将其声明为物理主键，取决于所使用的工具以及这些表周围的数据库的其余部分.您还会有很多其他具有此表FK的表吗?如果是这样，则可能需要一个替代密钥.

Whether you actually declare that as your physical primary key though depends on the tools that you're using and the rest of the database around these tables. Will you have a lot of other tables that have FKs to this table? If so, then a surrogate key might be in order.

这篇关于是否具有N-M关系的复合主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！