logo标签列表

复合主键

sqldatabaseprimary-keycomposite
20

我正在设计一个用于存储来自多个不同来源的数据的数据库。这些数据实例由原始来源分配唯一的ID。每个我所存储的实例应该包含关于它来自哪个来源的信息，以及它与该来源相关联的ID。

举个例子，考虑下面展示问题的表格：

----------------------------------------------------------------
| source_id | id_on_source | data                              |
----------------------------------------------------------------
| 1         | 17600        | ...                               |
| 1         | 17601        | ...                               |
| 2         | 1            | ...                               |
| 3         | 1            | ...                               |
----------------------------------------------------------------
请注意，虽然对于每个源，id_on_source 是唯一的，但可能会在不同的源中找到相同的 id_on_source。
我对关系型数据库有一定的了解，但远非专家或经验丰富的用户。我在这个设计中面临的问题是，应该使用什么作为主键。数据似乎要求使用复合主键(source_id, id_on_source)。经过一些搜索后，我发现关于复合主键的利弊存在一些激烈的争论，让我有些困惑。
该表将与其他表具有一对多的关系，并因此被其他表中的外键引用。
我没有被绑定到特定的RDBMS，并且我不确定是否重要，但假设我更喜欢使用SQLite和MySQL。
在这种情况下使用复合外键的利弊是什么？你更喜欢哪一个？
(Tips: 由于原文中存在英文缩写和专业术语，为了保持准确性，本次翻译中仍然采用了一些专业词汇)
8个回答
32

个人认为复合主键很痛苦。对于每个要与您的“来源”表联接的表，您都需要添加source_id和id_on_source字段。

我建议在您的“来源”表上创建一个标准的自增主键，并在source_id和id_on_source列上添加唯一索引。

这样，您就可以在其他表中只添加源表的ID作为外键。

通常情况下，我发现许多框架和工具产品对于复合主键的支持最好也只是“补丁式”的，而在其他情况下根本不存在。

考虑使用复合主键来存储时间纪元和时间戳 (1, 19702106) (2, 21062242)。由于 INT8、INT16、INT32 和 INT64 是基于二进制和位数的大小，因此对于公元9999年这一年份，我们没有适当的 INT 大小。INT 不够用，BIG INT 又太大了。 - AbbasAli Hashemian
14

组合键很难管理，连接速度较慢。因为你正在构建一个汇总表，所以使用代理键（即自动增量/标识列）。保留你的自然键列。

这还有很多其他好处。主要是，如果你与一家公司合并，他们有相同的数据源，但是重复使用了键值，如果你没有使用代理键，那么你就会遇到麻烦。

这是数据仓库中广泛认可的最佳实践（比你目前做的事情要大得多，但仍然相关），原因充分。代理提供数据完整性和快速连接。使用自然键作为标识符可能会让你很快陷入困境，因此只在导入过程中使用自然键。

3
你所说的问题是什么？如果在合并中出现冲突，你是否更希望出现错误而不是重复数据？请注意，我会尽力使翻译通俗易懂但不改变原意。 - Jeff Davis
2
@JeffDavis 没错，就我所知，代理键会引入冗余。 - Stephan Kristyn
你能解释一下为什么复合键在联接时会变得很慢吗？ 我试图理解为什么我实际上不使用复合键。 如果我有一个表引用另一个具有复合键（A，B）的表，我实际上并不需要在整个主键上进行连接。 我也可以写 ON（a.A = another.A），对吧？ 那么是什么使这变得更慢呢？ - Stefan Falk
3
只要正确地索引了复合键（Composite Keys），与代理键（Surrogate Key）相比，它们在连接时不应该更慢——考虑到主键（PK Indexes）会索引其所有字段，我不相信Eric所说的话——我想看到被分析的查询和基准测试，以定量证明复合键比代理键更慢。 - Dai
@Dai 谢谢你的澄清。我对数据库系统并不是很深入了解，但我不会想到 CKs 在连接时会很慢 - 如果它们像你说的那样被正确索引，为什么会这样呢？我也不确定它们是否如此难以管理。如果您有非常深层次的依赖关系，在每个层次中都继承了依赖表的 CK，那么情况可能会变得很丑陋，但我还没有遇到过这个问题。 - Stefan Falk
8
您有一个业务需求，即这两个属性的组合是唯一的。因此，您应该在这两个属性上设置“UNIQUE”约束。是否将该“UNIQUE”约束称为“主要”仅仅是一种偏好，它除了文档之外并没有太大的影响。
唯一的问题是是否添加额外的列并将其标记为“UNIQUE”。我能看到的唯一理由是性能，这是一个合法的理由。
就个人而言，我不喜欢将每个数据库都转化为本质上是图形的方法，其中生成的列本质上是指针，并且您只是从一个指向另一个。我认为这样扔掉了关系系统的所有伟大优点。如果你回过头来想一想，你引入了一堆对你的业务完全没有意义的列。您可能会对我的相关博客文章感兴趣。
6

我认为复合键创建了一个非常自然和描述性的数据模型。我的经验来自于Oracle，我认为在创建复合主键时没有任何技术问题。实际上，任何分析数据字典的人都会立即了解表格的某些内容。在您的情况下，显然每个source_id必须具有唯一的id_on_source。

使用自然键通常会引起激烈的争论，但我与之合作的人们从良好的数据模型角度喜欢自然键。

1
是的，但是如果你需要在两个、三个、四个条件上进行连接，从子表连接到主键往往会变得混乱，并且会使主键和所有索引膨胀。虽然这种方法可能感觉自然，但实际上并不是一个好主意。 - marc_s
2
点已经被接受。通常情况下，您会发现主要实体将具有由数据库生成的唯一键。例如，客户表具有CustomerId。通常是次要相关表具有复合键，并且大多数表没有引用它们的FK。例如，如果您存储客户电话号码的历史记录，则在Customer_contact_history表中，列CustomerId、phone、changedate可能是复合PK，因为这3个内容自然上是唯一的。 - softveda
3
基本上，我只在高阶部分的关键字是另一个表的关键字时使用复合主键。例如，我可能会创建一个OrderId + LineNumber的主键的OrderLineItem表。由于对OrderLineItem表的许多访问将是“order join orderlineitem using (orderid)”或其某个变体，因此这通常很方便。当查看数据库转储以确定哪些订单连接到哪些行项目时，这也很容易。
正如其他人所指出的那样，在大多数其他情况下，复合键都很麻烦，因为您的联接必须涉及所有部分。这意味着要输入更多内容，从而增加了错误的可能性，查询速度较慢等等。
两个部分的键不错；我经常这样做。我不愿使用三部分键。超过三个部分，我会说忘了它。
在您的示例中，我认为使用复合键没有什么好处。只需发明一个新的序列号，让源和源密钥成为普通属性即可。
2

添加额外的ID列将导致您需要强制执行两个唯一性约束，而不是一个。

在其他引用表中使用该额外的ID列作为外键，而不是自然存在的键，将导致您需要进行更多的连接操作，即在所有需要原始source_ID加上ID_on_source以及来自引用表的数据的情况下。

在这个应用程序中，你需要强制唯一性吗？如果你从其他系统获取数据，那么强制唯一性应该是他们的问题。这归结于你需要完成什么任务。 - Jay
关于额外的连接：我会将源和id_on_source保留在同一张表中，无论它是否是主键。我不认为有任何理由需要第二个查找表来进行翻译。将所有内容保持在一起。 - Jay
2

我曾经在使用许多复合键时遇到了问题，因此我不建议这样做（详见下文）。我还发现，在试图回滚用户错误时，独立/代理键（而不是自然键）有益处。问题在于通过一组关系，一个表连接了两个表，其中对于每行，复合键的一部分相同（这在第三范式中是适当的 - 父级的两个部分之间的比较）。我在连接表中去重了复合关系的那部分（因此，不再有parent1ID，other1ID，parent2ID，other2ID，而是parentID，other1ID，other2ID），但现在该关系无法更新主键的更改，因为它尝试通过每条路线进行两次更新，并在中途失败。

1

有些人建议您使用全局唯一标识符（GUID）：合并复制和事务复制使用uniqueidentifier列来保证在表的多个副本中唯一标识行。如果该值在创建时是全局唯一的，则无需添加source_id以使其唯一。

尽管uniqueid是一个很好的主键，但我同意通常最好使用不同的、自然的（不一定是唯一的）键作为聚簇索引。例如，如果uniqueid是标识员工的PK，您可能希望聚簇索引是部门（如果您的选择语句通常检索给定部门中的所有员工）。如果您确实想使用uniqueid作为聚簇索引，请参见NEWSEQUENTIALID()函数：它创建连续的uniqueid值，这些值（连续）具有更好的聚集性能。

在 SQL Server 中，一定要小心，不要将 GUID 主键作为表的聚集键（默认情况下是这样的）。请参阅 Kim Tripp 的优秀文章，了解为什么不要这样做：http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx。 - marc_s
关于GUID：如果要求仅是为每个记录拥有唯一的ID，那么这将起作用。但是，如果您需要知道源是什么，那么您必须将源ID发布到记录中，或者在其他地方拥有一个查找表（不好），或者在所有可能的源中搜索该GUID（更不好）。如果您必须始终保留源ID，则GUID没有任何价值。 - Jay
除了不需要复合主键（源 ID 不需要成为主键的一部分）之外，它没有任何价值。 - ChrisW
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接