SQL Server可空字段非空值唯一性的实现方案

问题分析与测试

假设我们有一张测试表 test_tb，其中 Caption 字段为可空字符串：

CREATE TABLE test_tb
(
    TestId int not null identity(1,1) primary key,
    Caption nvarchar(100) null
);
GO

如果在该字段上直接创建普通唯一索引：

CREATE UNIQUE NONCLUSTERED INDEX un_test_tb
ON test_tb(Caption)
GO

尝试插入多个 NULL 值时：

INSERT INTO test_tb (Caption) VALUES (null);
INSERT INTO test_tb (Caption) VALUES (null);

系统会报错 消息 2601...不能在具有唯一索引的对象中插入重复键的行。这是因为在 SQL Server 的标准逻辑中，NULL = NULL 被视为相等，导致唯一约束将多个 NULL 视为重复值。然而，业务需求通常是：NULL 允许多个，但具体的非空值只能出现一次。

通用解决方案：自定义函数 + CHECK 约束

为了兼容所有版本的 SQL Server，可以通过创建一个验证函数，并在表级别添加 CHECK 约束来实现。这种方法通过显式逻辑过滤掉 NULL 值的冲突检查。

第 1 步：创建验证逻辑函数

目的：编写一个标量函数，当表中存在当前即将插入的非空值的重复记录时返回 0，否则返回 1。

CREATE FUNCTION [dbo].[fn_CK_test_tb_Caption]()
RETURNS BIT
AS
BEGIN
    IF(EXISTS(
        SELECT 1 
        FROM test_tb AS a 
        WHERE (Caption IS NOT NULL) AND EXISTS (
            SELECT 1 AS Expr1 
            FROM test_tb 
            WHERE (Caption IS NOT NULL) AND (Caption = a.Caption) AND (a.TestId <> TestId)
        )
    ))
    RETURN 0
    RETURN 1
END
GO

第 2 步：应用 CHECK 约束

入口：数据库管理工具或脚本编辑器。

动作：向表中添加约束，调用上述函数进行校验。

ALTER TABLE test_tb
ADD CONSTRAINT CK_test_tb_Caption CHECK (dbo.fn_CK_test_tb_Caption() = 1)
GO

第 3 步：效果验证

首先测试插入多个 NULL 值，观察是否成功：

INSERT INTO test_tb (Caption) VALUES (null);
INSERT INTO test_tb (Caption) VALUES (null);
SELECT * FROM test_tb;

结果：成功执行，表中可以有多行 Caption 为 NULL。

接着测试非空值的唯一性：

INSERT INTO test_tb (Caption) VALUES (N'AAA'); -- 成功
INSERT INTO test_tb (Caption) VALUES (N'BBB'); -- 成功
INSERT INTO test_tb (Caption) VALUES (N'BBB'); -- 失败报错

结果：第三个语句因违反约束而终止，确保了非空值的唯一性。

高效解决方案：筛选索引 (Filtered Index)

如果您使用的是 SQL Server 2008 或更高版本，官方提供了更优雅且性能更好的原生解决方案——筛选索引。筛选索引是一种经过优化的非聚集索引，它只对表中符合谓词条件的部分行建立索引，完美契合此场景。

操作命令

只需一行代码，指定索引覆盖的范围不包含 NULL：

CREATE UNIQUE NONCLUSTERED INDEX un_test_tb
ON test_tb(Caption)
WHERE Caption IS NOT NULL
GO

优势对比

• 维护成本低：无需编写和维护复杂的函数代码。
• 性能更优：索引结构更小，查询和写入时的资源消耗低于基于函数的约束。
• 语义清晰：直接在 DDL 中表达了“非空即唯一”的业务规则。

以上就是 SQL Server 解决可空字段非空值唯一性的详细内容，更多关于数据库约束设计与优化的资料请关注本站其它相关文章！