logo标签列表

链路表需要一个没有意义的主键字段吗？

sqldatabase-designprimary-key
36

我正在处理一些链接表的工作，然后想到（危险！危险！）链接表的可能结构及其优缺点。

我提出了一些可能的连接表结构：

传统的三列模型

  • id - 自动编号的主键
  • table1fk - 外键
  • table2fk - 外键

这是一种经典的模型，在大多数书籍中都有介绍，就这样吧。

索引的三列模型

  • id - 自动编号的主键
  • table1fk - 外键 INDEX ('table1fk')
  • table2fk - 外键 INDEX ('table2fk')

根据我的经验，在传统模型中，您查询的字段没有被索引。我发现为外键字段建立索引确实可以提高性能，这是可以预期的。这不是一个重大的改变，但是是一个很好的优化策略。

复合键的两列 ADD PRIMARY KEY ('table1fk' , 'table2fk')

  • table1fk - 外键
  • table2fk - 外键

对于这个选项，我使用一个组合键来使来自table1的记录仅能与table2上的一条记录关联。由于键是组合的，所以我可以添加记录 (1,1)、(1,2)、(2,2) 而不会产生重复错误。

复合键两列选项有潜在的问题吗？它可能导致索引问题吗？性能会受到影响吗？有什么因素使其不能作为可能的选项吗？

1
这与https://dev59.com/lErSa4cB1Zd3GeqPX6GI相同。 - Ralph Lavelle
你说得对，很奇怪，我在主题搜索中没有找到它。 - Tyson of the Northwest
12个回答
22

我会使用复合键，而不使用额外的无意义键。

我不会使用强制规定该规则的ORM系统来操作我的数据库结构。

此外，我尽量避免使用IDENTITY/AUTO_INCREMENT字段，因为它们往往会使表格操作更加困难，且控制力较弱。但在这种情况下，这并不是重点，它们确实有其作用。 - Brimstedt
1
我也不会选择使用强制执行该规则的ORM，但在某些工作中（比如我的工作），有时你无法选择。我每年要处理近40个项目，其中并非所有项目都是从头开始的，而是对现有客户站点进行增强，其中已经存在ORM。 - Matt Dawdy
这取决于您的使用情况。 - Brimstedt
16

对于真正的链接表，在我的对象模型中通常不存在作为实体对象。因此，替代键从未被使用。从集合中删除项目会导致从已知两个外键的链接关系中删除项目 (Person.Siblings.Remove(Sibling) 或 Person.RemoveSibling(Sibling) 适当地在数据访问层中转换为 usp_Person_RemoveSibling(PersonID, SiblingID))。

正如Mike所提到的，如果它确实成为你的对象模型中的实体，则可能有资格拥有一个ID。然而，即使添加了时间因素，如关系的开始和结束日期等，情况也并不总是清楚的。例如，集合可以在聚合级别上关联有效日期，因此关系本身仍然可能没有任何公开属性的实体。

我想补充一点，你可能需要在两个外键列上双向索引该表。

13
如果这是一个真正的多对多关联表，则可以删除不必要的id列（除非你的ORM需要。在这种情况下，你必须决定你的智慧是否能够胜过你的实用性）。
但我发现真正的连接表非常罕见。通常不久之后我就开始想将其他数据放入该表中。因此，我几乎总是从一开始就将这些连接表建模为实体，并在其中添加一个id。
1
这是关于它们的初始和后续实用性的有价值的见解。谢谢。 - Kevin Danikowski
11

在灾难恢复情况下，拥有一个单列主键可以大有裨益。因此，虽然在理论上只需要两个外键是正确的，但在实践中，当遇到困难时，您可能希望使用单列主键。我从来没有因为有单列标识符而陷入麻烦的情况，但我曾经因为没有而陷入麻烦。

8
谢谢指出这点，我知道应该有一个单独的主键来实现灾难恢复，但我不知道它如何实际帮助。请问您能否详细说明一下拥有单独的主键如何有所帮助，并提供一些相关资料？+1 - Ferit Buyukkececi
4

组合主键和关闭集群。

3
你能详细说明一下聚类吗？为什么要关闭它？ - Brimstedt
4
当您创建主键时，默认情况下它会被创建为一个聚集索引。如果您的主键将主要按排序顺序插入或查找，那么这是可以接受的。但对于像这样的链接表，关系很可能不会以任何有意义的顺序出现，因此SQL需要更多的工作来维护这张表。 - Josh
3

我使用了复合键来防止重复记录，并让数据库处理异常。如果只有一个键，你需要依赖前端应用程序在添加新记录之前检查数据库中是否有重复。

2
有一种叫做“有标识”和“无标识”关系的概念。在“有标识”关系中，外键是多对多表中主键的一部分。例如，假设我们有表格Person、Company和一个多对多表格Employment。在“有标识”关系中，外键PersonID和CompanyID都是主键的一部分，因此我们不能重复使用PersonID, CompanyID组合。
TABLE Employment(PersonID int (PK,FK), CompanyID int (PK,FK))

现在，假设我们想要记录就业历史，这样一个人可以离开一家公司，在其他地方工作，然后再回到同一家公司。这里的关系是非标识的，PersonID, CompanyID 的组合现在可以重复，因此表格看起来会像这样：

TABLE Employment(EmploymentID int (PK), PersonID int (FK), CompanyID int (FK), 
                     FromDate datetime, ToDate datetime)
谢谢你在回答中提供了原因和背景。这对于将建议与当前困境形象化非常有帮助。 - burf
1

如果您正在使用ORM来获取/更改数据，则其中一些需要单列主键（感谢Tom H指出这一点）才能正常工作（我相信Subsonic 2.x是这样的，不确定3.x是否也是如此）。

在我看来，拥有主键对性能没有任何可衡量的影响，因此我通常会使用它。

2
通过“主键”，我猜你的意思是“单列主键”。这是一个非常重要的区别。复合主键也是主键。 - Tom H
1
糟糕！那正是我想表达的。感谢您的澄清。我会编辑答案以显示这一点。 - Matt Dawdy
0

如果您需要在联接表中“双向遍历”，即仅从table1fk或table2fk键开始，您可以考虑添加第二个反转的复合索引。

ADD KEY ('table2fk', 'table1fk')
0
我（几乎）总是使用额外的单列主键。这通常使构建用户界面更容易，因为当用户选择特定的链接实体时，我可以用一个整数值来标识，而不必创建并解析复合标识符。
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接