SQL Server如何实现允许重复空字段但不允许空字段值重复

在SQL Server的数据库设计过程中，我们经常会遇到这样的业务需求：某个字段可以允许为空，空值可以重复出现，但是一旦该字段填入了具体的值，这个值就不能和其他非空记录重复。比如用户表的备用手机号字段，不是所有用户都有备用手机号，没有的可以留空，但是有备用手机号的用户的该字段值必须唯一。如果直接给这个字段添加普通唯一约束，会发现所有空值都会被判定为重复值，导致只能插入一条空值记录，这显然不符合我们的需求。

普通唯一约束的局限性

SQL Server的普通唯一约束会对字段的所有值生效，包括空值。在SQL Server中，空值被视为相等的值，因此如果给字段添加了唯一约束，那么最多只能有一条记录的该字段为空，后续插入空值都会触发唯一约束冲突错误。我们可以通过下面的示例来验证这个现象。

-- 创建测试表，给test_col字段添加唯一约束
CREATE TABLE TestTable (
    id INT PRIMARY KEY IDENTITY(1,1),
    test_col NVARCHAR(50) NULL,
    UNIQUE (test_col)
);

-- 第一条空值插入成功
INSERT INTO TestTable (test_col) VALUES (NULL);

-- 第二条空值插入失败，触发唯一约束冲突
INSERT INTO TestTable (test_col) VALUES (NULL);

筛选唯一索引实现需求

SQL Server从2008版本开始支持筛选索引，我们可以创建一个筛选唯一索引，只对非空值生效唯一约束，空值不会被索引包含，自然也就不会触发唯一约束冲突。这是实现该需求最推荐的方式，性能最好且维护成本最低。

-- 删除之前的测试表和约束
DROP TABLE IF EXISTS TestTable;

-- 创建测试表
CREATE TABLE TestTable (
    id INT PRIMARY KEY IDENTITY(1,1),
    test_col NVARCHAR(50) NULL
);

-- 创建筛选唯一索引，只对test_col不为空的记录生效
CREATE UNIQUE INDEX IX_TestTable_TestCol_NotNull 
ON TestTable(test_col) 
WHERE test_col IS NOT NULL;

-- 插入多条空值，均成功
INSERT INTO TestTable (test_col) VALUES (NULL);
INSERT INTO TestTable (test_col) VALUES (NULL);

-- 插入非空值，第一次成功
INSERT INTO TestTable (test_col) VALUES ('value1');

-- 再次插入相同的非空值，触发唯一约束冲突，失败
INSERT INTO TestTable (test_col) VALUES ('value1');

-- 插入不同的非空值，成功
INSERT INTO TestTable (test_col) VALUES ('value2');

其他实现方式

触发器实现

如果不支持筛选索引的版本，也可以通过触发器来实现该需求。在插入和更新数据时，检查非空值是否已经存在，如果存在则回滚操作。

-- 删除之前的测试表
DROP TABLE IF EXISTS TestTable;

-- 创建测试表
CREATE TABLE TestTable (
    id INT PRIMARY KEY IDENTITY(1,1),
    test_col NVARCHAR(50) NULL
);

-- 创建触发器，处理插入和更新操作
CREATE TRIGGER TR_TestTable_CheckTestCol
ON TestTable
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    -- 检查插入或更新的非空值是否已经存在
    IF EXISTS (
        SELECT 1 
        FROM TestTable t
        INNER JOIN inserted i ON t.test_col = i.test_col
        WHERE i.test_col IS NOT NULL
        GROUP BY t.test_col
        HAVING COUNT(*) > 1
    )
    BEGIN
        RAISERROR('test_col的非空值不能重复', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;

-- 插入多条空值，均成功
INSERT INTO TestTable (test_col) VALUES (NULL);
INSERT INTO TestTable (test_col) VALUES (NULL);

-- 插入非空值，第一次成功
INSERT INTO TestTable (test_col) VALUES ('value1');

-- 再次插入相同的非空值，触发器报错，回滚，插入失败
INSERT INTO TestTable (test_col) VALUES ('value1');

计算列+唯一约束实现

还可以通过添加计算列的方式，将空值转换为唯一的值，非空值保持原值，再给计算列添加唯一约束。不过这种方式会额外占用存储空间，且计算逻辑需要保证空值转换后的值不会和正常的非空值冲突。

-- 删除之前的测试表
DROP TABLE IF EXISTS TestTable;

-- 创建测试表，添加计算列
CREATE TABLE TestTable (
    id INT PRIMARY KEY IDENTITY(1,1),
    test_col NVARCHAR(50) NULL,
    -- 计算列：空值转换为id的拼接值，非空值保持原值
    test_col_unique AS (
        CASE WHEN test_col IS NULL THEN CAST(id AS NVARCHAR(50)) ELSE test_col END
    ),
    -- 给计算列添加唯一约束
    UNIQUE (test_col_unique)
);

-- 插入多条空值，均成功
INSERT INTO TestTable (test_col) VALUES (NULL);
INSERT INTO TestTable (test_col) VALUES (NULL);

-- 插入非空值，第一次成功
INSERT INTO TestTable (test_col) VALUES ('value1');

-- 再次插入相同的非空值，触发唯一约束冲突，失败
INSERT INTO TestTable (test_col) VALUES ('value1');

不同实现方式对比

我们可以通过下面的表格来对比三种实现方式的优劣，方便根据实际场景选择：

注意事项

• 筛选唯一索引的筛选条件要和查询条件匹配，否则可能无法命中索引导致查询性能下降。
• 如果使用触发器实现，要注意处理批量插入的场景，避免触发器逻辑只处理单条记录。
• 计算列的方式如果转换逻辑不合理，可能会出现空值转换后的值和非空值冲突的情况，需要提前做好值范围的评估。
• 无论使用哪种方式，都要在业务层面做好校验，避免依赖数据库约束作为唯一的校验手段。

SQL_Server唯一约束空值处理数据库设计修改时间：2026-07-03 15:45:15