SQL Server的唯一键和唯一索引会将空值（NULL）也算作重复值

我们先在SQL Server数据库中，建立一张Students表：

CREATE TABLE [dbo].[Students](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [StudentCode] [nvarchar](50) NULL,
    [Name] [nvarchar](50) NULL,
    [Age] [int] NULL,
    [Sex] [nvarchar](5) NULL,
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_UniqueStudentCode] UNIQUE NONCLUSTERED 
(
    [StudentCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

该表的主键是列ID，该表的唯一键IX_UniqueStudentCode要求列StudentCode不能有重复值，但是列StudentCode可以为空值（NULL）。

 

首先我们插入四条数据到Students表中：

INSERT INTO [dbo].[Students]([StudentCode],[Name],[Age],[Sex])
VALUES
(N'S001',N'Jim',15,N'M'),
(N'S002',N'Tom',16,N'F'),
(N'S003',N'Bill',15,N'M'),
(NULL,N'Jerry',15,N'M')

可以看到我们在最后第四条数据中，给列StudentCode插入了空值（NULL）。

 

接着我们再插入一条列StudentCode为空值（NULL）的数据到Students表：

INSERT INTO [dbo].[Students]([StudentCode],[Name],[Age],[Sex])
VALUES
(NULL,N'Clark',16,N'M')

会发现SQL Server报错，错误如下：

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'IX_UniqueStudentCode'. Cannot insert duplicate key in object 'dbo.Students'. The duplicate key value is (<NULL>).
The statement has been terminated.

从错误中，我们可以看到，我们插入的数据违反了唯一键约束IX_UniqueStudentCode，插入了重复的空值（NULL）到Students表。

 

所以SQL Server的唯一键和唯一索引会将空值（NULL）也算作重复值，我们可以将上面的唯一键IX_UniqueStudentCode删掉，将其建立为唯一索引：

ALTER TABLE [dbo].[Students] DROP  CONSTRAINT [IX_UniqueStudentCode]

CREATE UNIQUE NONCLUSTERED INDEX [IX_UniqueStudentCode] ON [dbo].[Students]
(
    [StudentCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

会得到相同的实验结果。