NULL 和 FK 关系意味着什么 - 数据库

Question

我在关系 SQL 数据库中创建 FK 关系时遇到了困难，经过工作中的简短讨论，我们意识到我们有可为空的列，这很可能是导致该问题的原因。 我一直认为 NULL 意味着未分配、未指定、空白等，并且确实从未见过这样的问题。

与我交谈的其他开发人员认为，处理这种情况的唯一方法是，如果两个实体之间确实存在关系，那么您必须创建一个表来连接两个实体的数据......

对我来说，这似乎很直观至少可以说，对于包含来自另一个表的 ID 的列，如果该列不为空，则它必须具有来自另一个表的 ID，但如果它为 NULL，则可以继续。 这本身似乎与某些人的说法和建议相矛盾。

处理两个表之间可能存在关系的情况的最佳实践或正确方法是什么，如果指定了一个值，那么它必须在另一个表中......

Answer 1

这是完全可以接受的，这意味着，如果该列有任何值，那么它的值必须存在于另一个表中。 （我看到其他答案另有说法，但我不敢苟同。）

想象一个车辆和引擎表，引擎尚未安装在车辆中（因此车辆 ID 为空）。 或者是带有主管列和公司首席执行官的员工表。

更新：根据 Solberg 的请求，这里是具有外键关系的两个表的示例，显示外键字段值可以为空。

CREATE TABLE [dbo].[EngineTable](
    [EngineID] [int] IDENTITY(1,1) NOT NULL,
    [EngineCylinders] smallint NOT NULL,
 CONSTRAINT [EngineTbl_PK] PRIMARY KEY NONCLUSTERED 
(
    [EngineID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[CarTable](
    [CarID] [int] IDENTITY(1,1) NOT NULL,
    [Model] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [EngineID] [int] NULL
 CONSTRAINT [PK_UnitList] PRIMARY KEY CLUSTERED 
(
    [CarID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[CarTable]  WITH CHECK ADD CONSTRAINT [FK_Engine_Car] FOREIGN KEY([EngineID])
REFERENCES [dbo].[EngineTable] ([EngineID])


Insert Into EngineTable (EngineCylinders) Values (4);
Insert Into EngineTable (EngineCylinders) Values (6);
Insert Into EngineTable (EngineCylinders) Values (6);
Insert Into EngineTable (EngineCylinders) Values (8);

-- 现在一些测试：

Insert Into CarTable (Model, EngineID) Values ('G35x', 3);  -- References the third engine

Insert Into CarTable (Model, EngineID) Values ('Sienna', 13);  -- Invalid FK reference - throws an error

Insert Into CarTable (Model) Values ('M');  -- Leaves null in the engine id field & does NOT throw an error 

Answer 2

我认为这场辩论是对象关系阻抗不匹配的另一个副产品。 一些 DBA 类型会迂腐地说，基于对关系代数语义的更深入理解，FK 中永远不允许 null，但应用程序开发人员会认为这使他们的领域层更加优雅。

“尚未建立”关系的用例是有效的，但对于空 FK，一些人发现它通过引入更复杂的 SQL 功能（特别是 LEFT JOIN）增加了查询的复杂性。

我见过的一种常见的替代解决方案是在每个表中引入一个“空行”或“哨兵行”，其中 pk=0 或 pk=1（基于 RDBMS 支持的内容）。 这允许您设计一个具有“尚未建立”关系的域层，但也避免引入 LEFT JOIN，因为您保证总会有一些东西可以加入。

当然，这种方法也需要勤奋，因为您基本上是在权衡 LEFT JOIN ，以便必须检查查询中哨兵行的存在，这样您就不会更新/删除它，等等。权衡是否合理是另一件事。 我倾向于同意，仅仅为了避免更花哨的连接而重新发明 null 似乎有点愚蠢，但我也在一个应用程序开发人员无法赢得与 DBA 辩论的环境中工作。

编辑

我删除了一些“事实上”的措辞，并试图澄清“失败”连接的含义。 @wcoenen 的例子是我个人最常听到的避免 null FK 的原因。 这并不是说它们像“破碎”那样失败了，而是失败了——有些人会认为——未能遵守最小意外原则。

另外，我把这个回复变成了一个维基，因为我基本上已经把它从原来的状态中删除了，并从其他帖子中借用了。

Answer 3

我强烈支持在 OLTP 系统中使用外键中的 NULL 来指示无父项的论点，但在决策支持系统中它很少能很好地工作。 最合适的做法是使用特殊的“不适用”（或类似）值作为子记录（在事实表中）可以链接到的父项（在维度表中）。

原因是，向下钻取/交叉等的探索性质可能会导致用户在仅仅要求提供更多信息时不理解指标如何变化。 例如，当财务数据集市包含产品销售和其他收入来源的组合时，深入到“产品类型”应该将非产品销售相关数据分类，而不是让这些数字从报告中删除，因为事实表和产品维度表之间没有连接。

Answer 4

当外键是复合外键时，会出现在外键列中允许空值的问题。 如果两列之一为空，这意味着什么？ 另一列是否必须与引用表中的任何内容匹配？ 通过简单的（单列）外键约束，您可以摆脱空值。

另一方面，如果两个表之间的关系是有条件的（两个实体都可以单独存在，但可能几乎巧合地相关），那么最好使用“连接表”（包含一个表）来对其进行建模。 FK 到引用表，另一个到引用表，并且具有自己的主键作为两个 FK 的组合。

作为连接表的示例，假设您的数据库包含俱乐部和人员表。 有些人属于某些俱乐部。 连接表将是club_members，并且将包含引用“people”表的人员的FK，并且将包含该人员所属俱乐部的另一个FK，并且人员和俱乐部的标识符的组合将是主键连接表。 （连接表的另一个名称是“关联”或“关联”表。）

Answer 5

我倾向于传达该专栏含义的设计。 就域而言，空值可能意味着任意数量的事物。 在相关表中放置一个表示“不需要”或“未选择”的值至少可以传达目的，而无需询问开发人员或查阅文档。

Answer 6

假设您需要生成所有客户的报告。 每个客户都有一个国家/地区的 FK，并且国家/地区数据需要包含在报告中。 现在假设您允许 FK 为 null，并且执行以下查询：

SELECT * FROM customer, country WHERE customer.countryID = country.ID

任何国家/地区 FK 为 null 的客户都将从报告中默默忽略（您需要使用 LEFT JOIN 来修复它）。 我发现这不直观且令人惊讶，因此我不喜欢 NULL FK，并在我的数据库模式中避免使用它们。 相反，我使用哨兵值，例如特殊的“未知国家”。

Answer 7

CREATE TABLE [tree]
{
    [id] int NOT NULL,
    [parent_id] int NULL
};

ALTER TABLE [tree] ADD CONSTRAINT [FK_tree_tree] FOREIGN KEY([parent_id])
REFERENCES [tree] ([id]);

这并没有什么问题！ 根节点将永远有一个 NULL 父节点，这不是“尚未建立”关系的情况。 这里的连接也没有问题。

让根节点指向自身作为父节点以避免 NULL FK 或任何其他创造性的解决方法，意味着现实世界不再在数​​据库中准确建模。

没有人提到的一个潜在问题是包含大量 NULL 值的列上的索引性能。 虽然这本身与外键问题无关，但它可能会使连接表现不佳。

我确实明白，如果您是一名 DBA，正在处理拥有数亿行的超大型数据库，您将不会需要 NULL 外键，因为它们根本无法执行。 但事实是，大多数开发人员一生中永远不会使用如此大的数据库，而今天的数据库可以处理几十万行的这种情况。 强调一个（糟糕的）比喻，我们大多数人都不会驾驶 F1 赛车，而我妻子的雅阁中的自动变速箱可以很好地完成它需要做的事情（或者至少，它曾经是这样，直到几周前它坏了） ...）。

Answer 8

如果您将 NULL 分配给业务原因，那么您实际上是在重新定义 NULL 在您的域中的含义，并且必须为用户和未来的开发人员记录该含义。 如果存在将 NULL 作为外键的业务原因，那么我建议您按照其他人提到的那样进行操作，并添加一条具有“N/A”或“未分配”行值的连接记录。

此外，当数据库中的 NULL 现在变得具有多种含义（业务含义、某些错误或未正确输入）时，可能会出现复杂情况，这可能会导致问题更难以追踪。

Answer 9

如果字段可以为空，我不认为空值有问题。 滥用行为是在该字段中应该有信息时允许空值。

Answer 10

你做对了。 对于 FK，NULL 意味着没有值（意味着没有关系）。 如果 FK 中有一个值，它必须与其引用的 PK 中的一个值完全匹配。

允许这种情况并不一定是糟糕的设计。 如果关系是一对多且可选的，则完全可以在一侧的表中添加 FK，引用多侧的 PK。

如果关系是多对多，则需要一个自己的表，称为联结表。 该表有两个 FK，每个 FK 引用相关表之一中的一个 PK。 在这种情况下，可以通过简单地从连接表中省略整行来表达省略关系。

有些人的设计是为了避免允许 NULL 的必要性。 这些人将使用联结表来建立多对一关系，并在省略关系时省略一行，如上所述。

我自己并不遵循这种做法，但它确实有一定的好处。

Answer 11

我不得不说，尽管这显然是可能的，但根据 Jonathon Leffler 的精心阐述的观点，使用连接表有什么问题呢？

我提出这个问题是因为我有完全相同的需求，但我的设计现在通过连接表明显“干净”。 我的数据库图表现在清楚地表明我的字段是可选的，从模式 POV 来看这对我来说效果很好。

然后，为了简化查询，我只是创建了一个视图，将两个表左连接在一起，这给出了可选连接的外观，但实际上使用了更清晰的数据库结构。 在我看来，使用 ISNULL(MyField, 'None') 我可以提供“不存在”附加行设计的好处，但不会带来痛苦。

考虑到这里提到的几点，我同意 DBA 的观点——当您可以拥有更“可靠”的关系并且更容易与视图一起使用时，为什么要使用空列呢？ 而且也不需要真正的额外努力。

Answer 12

连接表是正确的方法。

键中的空值表明数据库设计不当。

空值是不是未分配/空/空白/等，它是丢失/未知的数据。

在外键字段中使用空值并不意味着“没有关系”，它意味着“我不知道是否存在关系”——这显然是不好的。