logo标签列表

多列主键？

database-design
7
例如，我有两个表，Users 和 UserRelations，它们之间存在一对多的关系。
对于 UserRelations 表，可以使用标识列作为主键：
[RelationID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[TargetID] [int] NOT NULL,

或者我可以将表格设计为：


[UserID] [int] NOT NULL,
[TargetID] [int] NOT NULL,

并将 UserID + TargetID 设为主键。

我的问题是采用每种设计的影响是什么，哪种更有利于性能？

1
+1，太好了，我不必浪费时间发同样的问题~ - dance2die
11个回答
8
如果您使用前一种设计，即有多余的identity列，那么就没有约束来防止插入两行具有相同UserID和TargetID的记录。您必须在其他两列上创建一个UNIQUE约束，这将创建一个组合索引。
另一方面，一些框架（例如Rails）坚持要求每个表都有一个名为id的代理键，因此“正确”的设计可能不起作用。这取决于您编写的代码来使用这个表格设计。
我们有时似乎是彼此的回声。 - dkretz
嘿！没问题，这应该对那些有类似问题的人很有帮助，因为会给出一致的答案，但措辞可能不同。 - Bill Karwin
@brian：引用UserRelations表的外键也可以是复合的。 - Bill Karwin
"其他两个表格"？你可能指的是 "其他两列"。 - Jonathan Leffler
你是在开玩笑吗？Rails 要求每个表都包含一个唯一的“id”列吗？这是他们的“约定优于配置”模因被推到了荒谬的极致吗？ - Jonathan Leffler
显示剩余2条评论
7

这几乎是一个宗教问题。每个主张使用非智能代理键的人都会有其他人指出，代理键可能是多余的等等。所以，你和你的团队应该做出最舒适的选择。

如果你决定使用代理键，则还应在自然（在本例中为多列）键上放置唯一约束，以保持数据的完整性。

我通常选择使用额外的代理键，因为自然键有时缺少许多理想的（但不一定必需的）主键特征：

  • 唯一值：主键必须唯一标识表中的每一行。
  • 非智能：主键最好没有嵌入语义含义。换句话说，它不应描述实体的特征。客户ID12345通常比RoadWarrior更好。
  • 不随时间变化：主键的值通常不会改变。更改主键值意味着更改实体的身份，这通常是没有意义的。我喜欢非智能键，因为它们不太可能发生变化。
  • 单属性：主键应具有尽可能少的属性。单属性主键是理想的，因为它们对应用程序更易于处理，并简化了外键的实现。
  • 数字：当唯一值为数字时，通常更容易管理。大多数数据库系统都具有内部例程，可以启用自动递增主键属性。

从性能角度来看，在大多数情况下，我怀疑没有太大的区别。但是像任何性能问题一样，你应该在关注的地方进行测量。

3
据我理解（实际上，在实践中这是有意义的），主键用于标识一些唯一的数据......至少在规范化的表中是这样。如果表中的数据需要通过键更明确地标识，则应该使用复合主键（具有多个列的主键）。例如，在一个存储当前和过去预约的表中，客户可能会出现在表中多次，您可以设置如下表格：
AppointmentDate，CustomerID，AppointmentReason
其中，AppointmentDate和CustomerID是复合主键，它们可以唯一标识AppointmentReason的信息。
我们使用AppointmentDate和CustomerID作为复合主键，因为多个客户可能在同一天同时预约。如果我们只使用AppointmentDate作为主键，则可能会遇到主键的唯一性限制问题。
对于您的情况，如果能提供更多包含的数据信息将有助于处理。但如果TargetID出现在User表中，我可能会将UserID和TargetID作为复合主键，并将TargetID作为外键添加到UserRelations表中。这样做的原因是，如果您只有一个名为RelationID的主键，那么您将得到一个重复的User列，这可能会影响性能，并且不会使您的表规范化。
2
您应该在每个表上始终尝试建立一个“有意义”或“自然”的主键或唯一索引，以帮助维护数据的完整性。如果这意味着多列（或“复合”）键，则确实会产生性能影响 - 特别是当将此多列键用作其他依赖表中的外键或用于查询中的搜索谓词等索引时。
当这些性能影响变得显著时（甚至在它们变得显著之前），您应该切换到使用非有意义的“代理”键（通常为整数），用于所有这些其他目的（FKs、连接、索引、查询搜索谓词、应用程序代码实体标识符等）。但是，始终保留有意义的键或唯一索引，以维护表的数据完整性。
2
我在描述关系的表中使用复合主键获得了良好的性能结果。声明主键有两个效果：
1. 您会得到约束，要求参与的每个列都是非空的，并要求参与的列一起具有唯一性。
2. 您会得到一个索引，可以快速访问给定主键的单个行。大多数 DBMS 会为您创建此索引。
这个索引的实用性取决于查询优化器、主键声明中列的顺序以及数据使用模式。有时，在复合主键的第一个列之外的其他列上创建自己的索引可以补充自动索引。
通过声明复合主键获得的约束通常比通过创建代理键并声明其为主键获得的约束更有用。
再次强调，以上所有内容都涉及描述实体之间关系的表。描述实体的表应该有一个简单的主键。最好使用自然键，但在给定数据无法提供可靠键的情况下，可能需要使用代理键。
1

我想要在这里提供我的意见，因为我相信其他答案的结合体中存在一种不太清晰的整体感。

混乱的原因在于Date，Codd等人设计的用于描述概念模型（即实体，属性，关系，键）的术语已被供应商用于描述该模型的物理实现的属性（表，列，记录，索引）。在讨论问题时，将两个领域保持分离和可识别是至关重要的。
第一范式规定每个概念模型中的关系都有一个主键。仅存在于物理模型中的“人工键”不符合条件；但请注意，一些所谓的人工键实际上并不在物理模型中，而是在概念模型中。 （其中一个例子是美国和加拿大的SSN / SIN，如果没有它，无法保证概念模型中的唯一性。）下面将详细介绍此问题，但为避免混淆，我将从现在起将来自概念模型的主键称为“自然键”。 （多个候选项将成为候选自然键。）
当前RDBMS实现中的“主键约束”具有几个用途。其中之一是对物理记录强制执行唯一性约束，以允许更新除此（物理）主键约束之外的所有字段；当自然键的字段随时间变化时，因此必须确保不包含任何这样的字段是物理模型中的主键约束。
另一个“主键约束”的用途是从相关表中启用外键查找，最有效的方法是使用最窄的“主键约束”。出于这个目的，自然键不是很好的“主键约束”选择，因为它们通常太宽（易于阅读），以至于索引大小和高度不够优化。
由此推断出一个推论：从概念模型到物理模型的自然键的最有效转换通常是唯一性约束和不同的、不相交的人工主键约束的组合。请注意，在需要自然键具有人工组件的情况下（例如SSN / SIN），希望在物理模型中完全存在额外的人工主键约束；这是因为必须允许更改和/或重新分配外部可见的人工编号。（有多少人知道美国的SSN在死亡几个月后被重新使用？）
1
主键是具有唯一性约束的索引组合。添加RelationID列将无法帮助您维护唯一性（因为仍然可以插入相同的UserID + TargetID对 - 它们只会获得不同的RelationIDs），也无法帮助数据访问（如果在用户和用户关系之间进行JOIN，则需要UserID上的索引）。因此，第二个方案似乎是更好的解决方案。
1
只是为了搅局，你可能需要在这里加上第三列：“关系”。每当我有一个用户：用户关系表时，我都会遇到两个用户有多个关系并且需要保持它们分开的情况。
David Jeff Mentor
David Jeff Sponsor

在某个时候，指导关系可能会结束，但您仍然需要赞助链接。虽然现在可能只有一种关系类型，但在将来可能会发生变化。因此，您的主键变成了用户ID、目标ID和关系类型。
0
假设您正在使用UserRelations表在用户和目标之间进行多对多关系，那么第一个选项是不正确的。您希望UserID + TargetID是唯一的，否则您可能会得到多个冗余条目加入相同的用户和目标。
0

将用户->目标关联的适当方法是您拥有的第二个选项，因为这是实际执行查找表引用完整性的方法。

如果没有主键或跨UserId和TargetId列唯一，可能会出现重复条目，这很可能会导致意外结果。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接