NULL和NULL应该与FK关系一起表示什么 - 数据库

我在关系型SQL数据库中创建FK关系时遇到了困难,在工作中进行了简短的讨论之后,我们意识到我们有可空列,这些列最有可能导致问题.我总是将NULL视为未分配,未指定,空白等等,并且确实从未见过这样的问题.

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

至少对于我来说,对于包含来自另一个表的ID的列来说,如果该列不为空,那么它似乎是直观的,那么它必须具有来自另一个表的ID,但如果它是NULL,那么这是正常的并且移动上.看起来这本身就与某些人的说法和建议相矛盾.

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

它是完全可以接受的,这意味着,如果该列具有任何值,则其值必须存在于另一个表中.(我看到其他答案断言否则,但我不同意.)

想一下车辆和发动机的表,并且发动机尚未安装在车辆中(因此VehicleID为空).或者是具有主管专栏和公司首席执行官的员工表.

更新:根据Solberg的请求,下面是两个具有外键关系的表的示例,表明外键字段值可以为null.

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 

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

"尚未建立"关系的用例是有效的,但是对于空FK,有些人发现它通过引入更复杂的SQL特性,特别是LEFT JOIN来增加查询的复杂性.

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

当然,这种方法也需要勤奋,因为你基本上要关闭LEFT JOIN,因为你必须在查询中检查你的哨兵行的存在,这样你就不会更新/删除它等等.无论是否合理的权衡是另一件事.我倾向于同意重新发明null只是为了避免一个更高级的加入似乎有点傻,但我也在一个应用程序开发人员没有赢得DBA辩论的环境中工作.

编辑

我删除了一些"事实问题"的措辞,并试图澄清"失败"连接的含义.@ wcoenen的例子是我个人经常听到的避免空FK的原因.并不是说它们像"破碎"那样失败,而是失败 - 有些人会争辩 - 坚持最不惊讶的原则.

此外,我把这个回复变成了一个维基,因为我基本上从原来的状态中剔除了它并借用了其他帖子.

我强烈支持外键中NULL的参数,以指示OLTP系统中的无父级,但在决策支持系统中,它很少能正常工作.最合适的做法是使用特殊的"不适用"(或类似)值作为子记录(在事实表中)可以链接的父(在维度表中).

这样做的原因是,向下钻取/跨越等的探索性质可能导致用户在他们仅仅询问有关它的更多信息时不能理解度量如何变化.例如,如果财务数据集市包含产品销售和其他收入来源的混合,那么深入到"产品类型"应该将非产品销售相关数据分类,而不是让这些数字从报告中删除,因为从事实表到产品维度表没有连接.

当外键是复合时,会出现在外键列中允许空值的问题.如果两列中的一列为空,这意味着什么？另一列是否必须匹配引用表中的任何内容？使用简单(单列)外键约束,您可以使用空值.

另一方面,如果两个表之间的关系是有条件的(两个实体可以独立存在,但可能几乎巧合地相关)那么最好用"连接表"对其进行建模 - 包含FK到引用的表,另一个到引用表,并且它有自己的主键作为两个FK的组合.

作为连接表的示例,假设您的数据库包含俱乐部和人员的表.有些人属于一些俱乐部.加入表将是club_members并且将包含引用"人员"表的人的FK,并且将包含该人所属的俱乐部的另一个FK,并且人和俱乐部的标识符的组合将是主要关键字.加入表.(加入表的另一个名称是'关联'或'关联'表.)