TSQL 多列唯一约束也允许多个空值 [英] TSQL Multi Column Unique Constraint That Also Allows Multiple Nulls

问题描述

我目前正在从 MS Access 迁移到 SQL Server.Access 允许在唯一索引中有多个空值，而 SQL Server 则不允许......我一直在通过删除 SQL Server 中的索引并添加过滤索引来处理迁移:

I am currently doing some migration from MS Access to SQL Server. Access allows multiple Nulls in unique indexes where as SQL Server does not... I've been handling the migration by removing the indexes in SQL Server and adding filtered indexes:

CREATE UNIQUE NONCLUSTERED INDEX idx_col1_notnull 
ON tblEmployee(col1) 
WHERE col1 IS NOT NULL;

我遇到的问题是我不确定如何实现复合或多列过滤"索引...或者这是否真的可行，因为我在研究中没有发现任何例子.

The problem I am having is that I am not sure how to implement a composite or multi-column "filtered" indexes... or if this is really possible as I've found no examples in researching it.

我确实有一个想法，通过像这样创建过滤索引来实现它:

I do have an idea to implement it by creating filtered indexes like so:

CREATE UNIQUE NONCLUSTERED INDEX idx_col1col2_notnull 
ON tblEmployee (col1, col2) 
WHERE col1 IS NOT NULL

然后添加第二个过滤索引:

And then adding a second filtered index:

CREATE UNIQUE NONCLUSTERED INDEX idx_col2col1_notnull 
ON tblEmployee (col1, col2) 
WHERE col2 IS NOT NULL

但我不确定这是否有效，更不用说是最好的方法了.非常感谢您提供正确方向的指导.

But I'm not sure if this would even work let alone be the best method. Guidance in the right direction would be greatly appreciated.

推荐答案

您可以添加以下索引以仅索引不可为空的列:

You can add the following index to index only non nullable columns:

create table tblEmployee(col1 int, col2 int)
go

create unique nonclustered index idx_col1col2_notnull ON tblEmployee(col1,col2) 
where col1 is not null and col2 is not null
go

--This Insert successeds
insert into tblEmployee values
(null, null),
(null, null),
(1, null),
(1, null),
(null, 2),
(null, 2)

--This Insert fails
insert into tblEmployee values
(3, 4),
(3, 4)

这篇关于TSQL 多列唯一约束也允许多个空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！