Foreign Key on unique index doesn't work with NULL column

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

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

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.

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?

Here's a little repro script:

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;

---

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.

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.

链接地址: http://www.djcxy.com/p/76312.html

上一篇: 改变表格添加列语法

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