SQL - 这种关系可以有一个主键吗？

Question

乐队和成员之间的关系，'MemberOf'，包括成员id(Mid)，乐队id(Bid)，'StartYear'，'EndYear'和演奏的'Instrument'。 Mid 和 Bid 分别是 Member 和 Band 关系的外键，但在这种情况下我很难确定“MemberOf”关系的主键。

注意:如果成员从开始就在乐队中，则开始年份可以为空，如果成员仍在乐队中，结束年份也可以为空。

在这种情况下，开始和结束年份不能用作 PK，因为它们可能包含空值，但成员 ID 和乐队 ID 的信息不足以确保唯一性 - 例如同一位成员稍后返回同一乐队。

这是否意味着不存在主键？谢谢。

Answer 1

这是一个常见问题。要了解发生了什么，我们需要从 SQL 结构 PRIMARY KEY 中分离出关系概念“主键”。

关系概念“主键”是表中行的唯一标识符。在您的表的上下文中，乐队和成员显然是主键的一部分，成员为乐队演奏的时期也是如此。一个成员可以在 2003-2005 年为特定乐队演奏，在 2006-2007 年开始单飞生涯，并在 2008-2011 年重新加入他原来的乐队。对于表中的成员，您需要两个条目，两个期间各一个。

理想情况下，您可以说“startYear”和“endYear”构成一个“memberFor”期间列，主键是 (bid, mid, memberFor)，对于 bid+mid 的单个组合， memberFor 值表示不相交的范围:即，对于两行 R1、R2，值 R1.memberFor 不重叠或接触 R2.memberFor。重叠示例:R1.memberFor = (2003-2005)，R2.Memberfor = (2004-2006)。触摸示例:R1.memberFor = (2003-2005)，R2.MemberFor = (2005-2007)。

理论到此为止。

实际上，SQL 不支持时间段，在此上下文中的时间段具有开始和持续时间。 INTERVAL 类型不符合条件，因为它们表示没有开始的持续时间，而 DATE、TIME、TIMESTAMP 类型不符合条件，因为它们缺少持续时间(忽略与手头问题无关的量化问题)。显然，您还可以用 (duration, end) 和 (start, end) 表示一个周期，在后一种情况下，您可以为开始和结束设置开、闭、开-闭或闭-开范围。

因为SQL不支持必要的类型，更不用说必要的检查选项，你必须自己做检查，而且做起来很复杂。 SQL 主键只查找值的相等性。不受约束，这意味着如果您在 (bid, mid, start) 或 (bid, mid, end) 或 (bid, mid, start, end) 上创建主键，您可能会在表中得到不需要的数据，这些数据满足SQL 主键而不是概念上的主键。

Bid     Mid     Start   End
  1       1      2003   2005   - α
  1       1      2004   2006   - β
  1       1      2004   2007   - γ
  1       1      2008   2011   - δ

将标记为 α 的行视为正确的。如果你的 SQL 主键是 on (bid, mid, start)，那么标记为 β 的行不应该被允许，因为它与 α 重叠；对于 γ 类似。然而，SQL 只是阻止了 β 和 γ 的同时出现；第一个输入的可以，第二个输入的将被拒绝。如果允许 γ，则不应允许 ∂，因为它接触 γ。类似地，如果 SQL 主键打开(bid、mid、start、end)，所有四行都将被允许进入表，但显然它们不应该。如果 SQL 主键打开(bid、mid、end)，则上面的所有行都将被允许。

因此，您必须使用过滤掉不需要的行的复杂查询来扩充 SQL 主键的条件。这可能是在表的插入触发器和更新触发器中使用的存储过程。

…细节留给读者练习…

在您的表中，您有一个基于年份的粒度。因此，您可能会修改一个仅存储 (mid, bid, memberInYear) 的表，并在每个乐队成员成为给定乐队成员时的每一年都有一行。这减少了一个大的量程时间段，其中三列上的 SQL 主键强制执行约束。但是，如果您更改表格的粒度以记录开始日期和结束日期(精确到天)，那么显然修改后的设计效果不佳。

因此，您可能应该在 (bid、mid、start) 上使用 SQL 主键，该主键由从插入和更新触发器调用的存储过程备份，该触发器强制执行非重叠和非接触标准。