唯一索引上的外键不适用于NULL列

I am struggling with a foreign key on a unique index in SQL Server, which isn't quite working as expected.

我在SQL Server中的一个唯一索引上使用外键时遇到了困难,这个索引并没有按预期工作。

My table structure is like this:

我的表结构是这样的:

Table mm1 has a nonclustered PK-Index on a surrogate key column id
- Additionally mm1 has a clustered unique index on three columns (one of them is nullable)
- Table mm2 links on those three columns via a foreign key

表mm1在代理键列id上具有非聚簇PK-Index另外mm1在三列上有一个聚簇唯一索引(其中一列可以为空)表mm2通过外键在这三列上链接

The creation of these objects doesn't throw any errors. However, the foreign key is not evaluted properly and allows for non-existing values in mm1 to be inserted in mm2. This seems to be related on the nullable column c....if I set the foreign key on just columns a and b it works as expected.

创建这些对象不会产生任何错误。但是,外键未正确评估,并允许将mm1中的不存在值插入mm2中。这似乎与可空列c相关....如果我只在列a和b上设置外键它按预期工作。

Can you explain this behaviour? Why allowing to define a unique index including null in the first place but not supporting it properly in foreign key constraints? Is there a way to achieve correct results without changing contents of table mm2?

你能解释一下这种行为吗?为什么允许首先定义一个包含null的唯一索引,但在外键约束中不能正确支持它?有没有办法在不改变表格mm2的内容的情况下获得正确的结果?

Here's a little repro script:

这是一个小的repro脚本:

CREATE table mm1 (
    id int identity(1,1) NOT NULL,
    a varchar(50) not null,     
    b int not null, 
    c int null, 
    PRIMARY KEY NONCLUSTERED(id))

CREATE table mm2 (
    id int identity(1,1) NOT NULL,
    a varchar(50) not null,
    b int not null,
    c int null,
    d varchar(10),
    PRIMARY KEY NONCLUSTERED(id))

CREATE  UNIQUE CLUSTERED INDEX  idx_mm1_mm_fkTest1 ON mm1(a,b,c)

ALTER TABLE mm2
ADD CONSTRAINT  fk_mm2_mm_fkTest1 
FOREIGN KEY (a,b, c)  REFERENCES mm1(a,b,c)

ALTER TABLE mm2 CHECK CONSTRAINT fk_mm2_mm_fkTest1;

INSERT INTO mm1 VALUES ('abc', 1, 2);

INSERT INTO mm2 
(a,b,d) VALUES('sa',1,'sad')

SELECT * FROM mm2;

1 个解决方案

#1

I am not at all sure what I would expect this to do. The more I think about it, the less sense it makes.

我完全不确定我会期待这件事。我想的越多,它的意义就越小。

NULL is evaluated in these contexts to mean UNKNOWN. With an unknown in a foreign key you can never be sure which row it refers to in the first place. In other words, this constraint makes no semantic sense.

在这些上下文中计算NULL表示UNKNOWN。对于外键中的未知,您可能永远无法确定它首先引用哪一行。换句话说,这种约束没有语义意义。

Use a default value and have your foreign key hit non-null values only. If you allow NULLs in foreign keys they will be dangling anyway.

使用默认值并使外键仅命中非空值。如果在外键中允许NULL,则无论如何都会悬空。

#1