如何在 NULL 列上创建唯一索引?

2021-12-21 00:00:00 indexing sql constraints unique sql-server

我使用的是 SQL Server 2005.我想将列中的值限制为唯一，同时允许 NULL.

I am using SQL Server 2005. I want to constrain the values in a column to be unique, while allowing NULLS.

我当前的解决方案涉及视图上的唯一索引，如下所示:

My current solution involves a unique index on a view like so:

CREATE VIEW vw_unq WITH SCHEMABINDING AS
    SELECT Column1
      FROM MyTable
     WHERE Column1 IS NOT NULL

CREATE UNIQUE CLUSTERED INDEX unq_idx ON vw_unq (Column1)

有更好的想法吗?

推荐答案

很确定你不能这样做，因为它违反了唯一性的目的.

Pretty sure you can't do that, as it violates the purpose of uniques.

然而，这个人似乎有一个体面的工作:http://sqlservercodebook.blogspot.com/2008/04/multiple-null-values-in-unique-index-in.html

However, this person seems to have a decent work around: http://sqlservercodebook.blogspot.com/2008/04/multiple-null-values-in-unique-index-in.html

相关文章