logo标签列表

为什么复合主键仍然存在？

mysqlsql-serverdatabase-design
58

我的任务是将一个数据库迁移到一款中档ERP系统中。新系统时而使用组合主键，从实际角度来看，这样做有何好处呢？

与自动生成的ID相比，我只能看到负面影响：

  • 外键变得模糊
  • 更难进行迁移或重新设计数据库
  • 在业务变化时不够灵活（我的汽车没有注册牌照..）
  • 通过约束条件可以更好地实现相同的完整性。

这似乎回归到候选键的设计概念，而我并不明白这样做的意义所在。

这是来自软件开发者社区的一种惯例/遗物吗？(用以最小化空间/索引)，还是我忽略了什么？

//编辑// 刚找到一个很好的stackoverflow文章：Composite primary keys versus unique object ID field //

13
候选键的作用是什么？ - Quassnoi
6
您可以强制某一列的数值唯一，而不必将其与主键绑定。按照问题中建议的方式，将主键设置为自动生成的ID，并确保需要具有唯一值的列强制执行该规则即可。 - Justin C
4
你确定你理解候选键的概念了吗？ - Quassnoi
7
对于“$person”关于“$subject”的居高临下的态度可能不会为“$condescendent”带来太大的好处（无论相对知识水平如何）。 - Quassnoi
3
@Quassnoi，为什么？在一个关系中，候选键是最小超键。空关系的唯一可能的候选键是 {} - 空集。然而，在关系变量中，候选键必须适用于所有可能的关系值，而不仅仅是当前状态。因此，关系变量的候选键只能通过参考其应满足的域约束、依赖关系和其他规则来确定。由于您没有指定示例要求的任何信息，因此我无法给出通用答案。 - nvogel
显示剩余16条评论
9个回答
57

当你的主键是非代理（non-surrogate）且本质上是复合的时候，需要使用组合键 (Composite keys)，即可拆分成几个不相关的部分。

一些现实世界的例子:

  • 多对多链接表，其中主键由关联实体的键组成。

  • 多租户应用程序，当 tenant_id 是每个实体主键的一部分时，并且只能在相同租户内链接实体(通过外键约束)。

  • 处理第三方数据的应用程序（已提供主键）

请注意，从逻辑上讲，所有这些都可以使用唯一约束(UNIQUE)实现 (除了代理主键(PRIMARY KEY))。

但是，有些实现具体问题：

  • 有些系统不允许 FOREIGN KEY 引用任何不是 PRIMARY KEY 的东西。

  • 有些系统只会把表聚集（cluster）在一个 PRIMARY KEY 上，因此使组合成为 PRIMARY KEY 将改进连接组合的查询性能。

@quassnoi，哪些系统不允许FOREIGN KEY约束引用除PRIMARY KEY约束以外的任何内容？哪些系统只能在PRIMARY KEY约束上进行聚集？我不知道有任何DBMS有这样的限制。 - nvogel
@Quassnoi - 很棒的回答。您能否再解释一下多租户情况是如何工作的？ - orokusaki
@dportas：Paradox 不允许引用除 PRIMARY KEY 以外的任何内容；使用 InnoDB 的 MySQL 和 Oracle 只能按照 PRIMARY KEY 来对表进行集群化（请注意，Oracle 称其为“索引组织表”，并且所谓的“CLUSTER”完全不同）。 - Quassnoi
@orokusaki：如果你有多租户的 posts 和 tags，而且没有将 tenant_id 作为可引用键（PRIMARY 或 UNIQUE）的一部分，那么 posts_tags 中的条目可以引用来自不同租户的 post 和 tag。为了正确处理它，你应该将 tenant_id 作为两个表中的一个键的一部分，将其添加到 posts_tags 中，并将其作为两个 FOREIGN KEY 约束的一部分添加到适当的表中。 - Quassnoi
@Quassnoi - 我的意思是像SQLAlchemy或Python中的ORM，抱歉。 - orokusaki
显示剩余4条评论
41

就我个人而言，我更喜欢使用代理键。但是，在连接仅由两个其他表的ID组成的表（以创建多对多关系）时，组合键是可行的，因此删除它们会使事情变得更加困难。

有一种观点认为，代理键总是不好的，如果您没有通过使用自然键记录唯一性，那么您的设计是不好的。我强烈反对这种观点（例如，如果您没有存储SSN或其他唯一值，我挑战你为个人表想出自然键）。但是，许多人认为这对于适当的规范化是必要的。

有时，具有组合键可以减少连接到另一个表的需要。有时不行。因此，组合键有时可以提高性能，有时也可能降低性能。如果键相对稳定，则可以在选择查询方面获得更快的性能。但是，如果它是某些东西的主题，例如公司名称，则当公司A更改其名称并且您必须更新100万个关联记录时，您可能会遇到麻烦。

在数据库设计中，没有一种解决方案适用于所有情况。有时组合键很有帮助，有时非常糟糕。有时代理键很有帮助，有时不是。

17
非常重要的提示：社会保障号码不是唯一的，因此不是一个好的选择。http://blogs.computerworld.com/node/5969 - BoffinBrain
3
候选键并非好坏之分，只有存在或不存在的区别。如果在表中存在两个或更多相同的“SSN”，那么“SSN”就不是候选键；否则，“SSN”就是候选键。 - Quassnoi
7
明白了。我会稍微更明确一些：即使您的表格没有重复的社保号码，它仍然不能被视为候选表格，否则您可能在未来受到惩罚（正如文章中所解释的那样）。 - BoffinBrain
2
@BoffinbraiN：某物是否为候选键并不是指在同一张表中存在两个相同的值（或元组），这意味着您在数据已经存在后才进行数据库设计！而是指可能的数据集（为了讨论，我们假设它是所有人）是否会产生重复值。在这方面，由于社会安全号码（SSN）不能保证在所有可能的人中是唯一的，因此SSN不是候选键。 - Adam Robinson
2
同意，平衡的答案加一。反模式不是不使用代理键，也不是不使用复合键。反模式是盲目地为每种情况使用其中一种解决方案。 - Bill Karwin
显示剩余3条评论
40

组合主键在作为其他表的外键时提供更好的性能，并减少了表读取 - 有时它们可以拯救生命。如果您使用代理键，则必须转到该表以获取自然键信息。

例如（纯粹的例子 - 所以我们不谈论DB设计），假设您有一个ORDER表和一个ORDER_ITEM。如果您在ORDER_ITEM中使用ProductId和LineNumber (更新：正如Pedro提到的，还有OrderId或者更好的是OrderNumber) 作为组合主键，则在您的交叉表SHIPPING中，您将能够在SHIPPING_ORDERITEM中使用ProductId。如果您需要找出所有需要发货的该ProductId的产品，这可以极大地提高性能，而无需连接。

另一方面，如果您使用代理键，则必须连接，结果会得到一个非常低效的SQL执行计划，其中必须在多个索引上进行书签查找。

请参见更多信息关于使用代理键成为主要问题的书签查找。

ProductId和LineNumber将组成复合键的表是哪个？ - Pedro
那么，如果您需要再次订购同一产品，您要怎么做？ - Pedro
1
当然，你也会在其中包含OrderId，就像我说的，我不是在谈论数据库设计。但是谢谢，我会添加OrderId。 - Aliostad
我将对数据进行反规范化，并将产品ID放入shipping_orderitem中。这样做不仅更快，而且可以保留产品表中历史变更的记录。 - Teson
9
自然主键是脆弱的。
假设我们在（CountryCode，PhoneNumber）上建立了一个基于自然PK的系统，并且几年后我们需要添加Extension，或将PK更改为一个列：Email。如果这些PK列被传播到所有子表中，这将非常昂贵。
几年前，有一些系统是基于社会安全号码作为自然主键构建的，当SSN变得不唯一和可空时，必须重新设计以使用标识。
因为我们无法预测未来，我们不知道是否稍后的某些变化会使曾经完全正确和完整的模型过时。
4
基于错误或不完整的建模的自然键是脆弱的。FTFY. （注：FTFY 是修正了/已解决的意思，来自英文“Fixed That For You”的缩写） - Adam Robinson
6
任何基于我们的理解“正确”或“完整”的方法都是脆弱的。FTFY. - Robert Rossney
5
你的论点基本上是指那些实际上并不是自然键的自然键不是好的自然键。重言反复，毫无意义？ - jdmichal
1
如果你的观点是自然键本质上是不好的，永远不应该使用，那么我不认为我们会达成一致。如果你的观点是自然键与代理键相比具有风险成本和效益，那么我们正在白费口舌。 - Adam Robinson
1
就你提出的具体例子而言，如果你雇了一位专家，并且这位专家说ISBN是一个完全可靠和合适的键，那么你的问题就在于筛选专家的人了 ;) - Adam Robinson
显示剩余12条评论
8
简而言之，组合键的目的是利用数据库来实施一个或多个业务规则。换句话说：保护您数据的完整性。
例如，您有一个从供应商那里购买零件的清单。您可以像下面这样创建您的供应商和零件表：
SUPPLIER
SupplierId
SupplierName

PART
PartId
PartName
SupplierId

哎呀，零件表允许重复数据。由于您使用了自动生成的代理键，因此未强制执行来自供应商的零件只能输入一次的事实。相反，您应该这样创建PART表：

PART
SupplierId
SupplierPartId
PartName

在这个例子中，您的零件来自特定的供应商，您希望在PARTS表中执行规则：“单个供应商只能提供一个零件一次”，因此需要使用复合键。您的复合键可以防止意外重复输入零件。
您可以始终将业务规则排除在数据库之外，并将其留给应用程序处理，但是通过在数据库中保留规则（通过复合键），您可以确保业务规则在任何地方得到执行，尤其是如果您决定允许多个应用程序访问数据。
这是一个很好的例子，但它忽略了对非主键字段建立约束的能力。你认为保持原始结构并在PartId + SupplierId上定义唯一约束有问题吗？ - HackSlash
8
非常简单的答案是数据完整性。如果数据要有用和准确，那么键值可能是必需的。拥有“自动生成的id”并不意味着不需要其他键。另一种选择是不强制唯一性，并接受数据将被复制并几乎肯定包含异常，从而导致错误。为什么你想要那样呢？
4
您可以在不将其设置为主键的情况下强制唯一性。如果您有自然键和代理键，则不添加自然键上的唯一索引将是不良设计。 - HLGEM
4
@HLGEM，“关键字”就是“关键字”，在“主关键字”和其他候选关键字之间没有区别。索引与此关系不大。关键字由“约束”来强制执行，而索引只是加速数据访问的一种方式。 - nvogel
1
如果给我的回答投了反对票的人能够解释一下原因，我将不胜感激。 - nvogel
4
简短回答：多列外键自然地参考多列主键。仍然可以有一个自动生成的id列作为主键的一部分。
哲学回答：主键是行的身份标识。如果有一些信息是行身份的固有部分（例如文章属于哪个客户，在多客户维基中），则该信息应是主键的一部分。
举个例子：组织局域网派对的系统
该系统支持多个局域网派对，参与者和组织者相同，因此：
```html CREATE TABLE lan_party ( party_id INT NOT NULL, person_id INT NOT NULL, PRIMARY KEY (party_id, person_id) ); ```
这里，`party_id` 和 `person_id` 组成了复合主键。
CREATE TABLE users ( users_id serial PRIMARY KEY, ... );

还有几个相关方：

CREATE TABLE parties ( parties_id serial PRIMARY KEY, ... );

但是大部分其他内容需要携带与哪个方（党）相关的信息：

CREATE TABLE ticket_types (
    ticket_types_id serial,
    parties_id integer REFERENCES parties,
    name text,
    ....
    PRIMARY KEY(ticket_types_id, parties_id)
);

这是因为我们想要引用主键。表attendances上的外键指向表ticket_types。

CREATE TABLE attendances (
    attendances_id serial,
    parties_id integer REFERENCES parties,
    ticket_types_id integer,
    PRIMARY KEY (attendances_id, parties_id),
    FOREIGN KEY (ticket_types_id, parties_id) REFERENCES parties
);
我认为我们都清楚什么是复合键，但OP问的是为什么人们仍然使用它们。 - Adam Robinson
2
@Adam：为什么不呢？从数据完整性的角度来看，在某些情况下它们是必要的。这个例子试图证明，这不是关于自动生成的主键与组合主键的区别。无论如何，都需要组合主键。 - jkj
4

就像函数封装一组指令，数据库视图抽象基表连接一样，代理键也把它们所放置的实体的含义进行了抽象。

例如，如果您有一个保存交通工具数据的表格，应用代理键VehicleId可以从数据角度抽象出交通工具的含义。当您引用VehicleId = 1时，您肯定在谈论某种类型的交通工具，但我们是否知道它是2008年雪佛兰Impala还是1991年福特F-150？不知道。无论Vehicle #1的底层数据何时更改都是可以的。

这是对主键的解释，而不是代理键的解释。 - Adam Robinson
我不同意。自然键实际上代表着实体的具体细节，而代理键则是对其进行抽象。也许我误解了重点。 - ses011
2

虽然我更喜欢使用代理键，但在某些情况下我会使用复合键。复合键可以完全或部分由代理键字段组成。

  • 多对多连接表。这些通常需要一个唯一的键对。在某些情况下，可能会在键中包含其他列。
  • 弱子表。像订单行这样的东西不能单独存在。在这种情况下，我在复合表中使用父（订单）表的主键。

当一个实体与多个弱表相关联时，在查询子数据时可能可以从连接集中消除一个表。在孙子表的情况下，可以将祖父与孙子连接而不涉及中间表。

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