如何创建唯一的键，也匹配null或空字段?

Question

我想创建某种"唯一约束"将null字段算作匹配。

CREATE TABLE person (
id int,
firstname varchar,
lastname varchar,
dob date,
primary key (id)
);

我想防止创建匹配精确值或空字段的副本。

的例子:

INSERT (john, doe, 2000-01-01);
INSERT (john, null, null); //should not be possible, there is already a 'john'
INSERT (null, doe, null); //should not be possible, there is already a 'doe'
INSERT (jane, doe, null); //should be possible, as there is no jane doe yet.

在持久化中，我想检查是否已经有匹配的条目:

WHERE (firstname='john' or firstname is null) and (lastname = 'doe' or lastname is null) and (dob = '2000-01-01' or dob is null)...

如果存在匹配，则阻止插入。我知道这不是一个真正的唯一键，但我可能在这里漏掉了正确的术语?

问题:是否存在某种通用这类问题的解决方案是什么?

Answer 1

你可以试试触发，像这样

CREATE TRIGGER [dbo].[person_Dup_Trigger] 
ON [dbo].[person]
AFTER INSERT  
AS 
BEGIN
SET NOCOUNT ON;
IF EXISTS (
SELECT 1
FROM dbo.person S
INNER JOIN Inserted I ON
-- Test for a duplicate
S.firstname = I.firstname
OR S.lastname = I.lastname
-- But ensure the duplicate is a *different* record - assumes a unique ID
AND S.ID <> I.ID
)
BEGIN
THROW 51000, 'should not be possible, there is already a record', 1; 
END;
END;

Answer 2

注意要检查的条件

WHERE (firstname='john' or firstname is null) and (lastname = 'doe' or lastname is null) and (dob = '2000-01-01' or dob is null)...

禁止任何空插入，例如，不可能插入

INSERT (john, null, null);

，即使它是第一次插入person表。这是你想要达到的目标吗?如果你只是想防止多个空值，我会尝试这个:

create unique index idx1 on person(coalesce(firstname, 'null'), coalesce(lastname, 'null'), coalesce(dob, 'null'));

编辑:上面的解决方案只适用于假设我们不期望文字值'null'表示firstName, lastName或dob:)，因为它不允许插入。

另一方面，如果你只希望非空值是唯一的(每个null对于MySQL DB是唯一的)，那么索引

create unique index idx1 on person(firstname, lastname, dob);

应该做这项工作。