SQL Server允许重复空字段不空字段值唯一

复制代码 代码如下:

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

复制代码 代码如下:

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

复制代码 代码如下:

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

复制代码 代码如下:

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

复制代码 代码如下:

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

复制代码 代码如下:

INSERT INTO test_tb (Caption)
VALUES (N'AAA')
GO
INSERT INTO test_tb (Caption)
VALUES (N'BBB')
GO
INSERT INTO test_tb (Caption)
VALUES (N'BBB')
GO
SELECT * FROM test_tb
GO

复制代码 代码如下:

CREATE UNIQUE NONCLUSTERED INDEX un_test_tb
ON test_tb(Caption)
WHERE Caption is not null
GO