假设要实现约束的列名为C1, 所在表名为MyTable
(1)除了SQL SERVER 以外的大型数据库都是允许 UNIQUE约束有多个空值的。
(2)SQL Server 2008中有了一个解决方案,那就是筛选索引。
CREATE UNIQUE NONCLUSTERED INDEX MyTable
ON MyTable(C1)
WHERE C1 is not null
GO
(3)SQL Server 2008以下版两个方案
A、使用触发器在插入和更新时控制
CREATE trigger Mytrigger on MyTable for insert, update as
BEGIN
IF (select max(cnt) from (select count(i.c1)
as cnt from MyTable, inserted i where MyTable.c1=i.c1 group
by i.c1) x) > 1
ROLLBACK TRAN
END
B、 在约束中使用自建函数来实现
创建验证逻辑函数
CREATE FUNCTION [dbo].[fn_CK_MyTable_C1]()
RETURNS BIT
AS
BEGIN
IF(EXISTS(
SELECT 1
FROM MyTable AS a
WHERE (C1 IS NOT NULL) AND EXISTS
(SELECT 1 AS Expr1
FROM MyTable
WHERE (C1 IS NOT NULL) AND (C1 = a.C1) )
))
RETURN 0
RETURN 1
END
GO
在约束中引用函数:
ALTER TABLE test_tb
ADD CONSTRAINT CK_MyTable_C1 CHECK (dbo.fn_CK_MyTable_C1() = 1)
GO
温馨提示:内容为网友见解,仅供参考