具有 UNIQUE 索引的列中的多个 NULL 值 [英] Multiple NULL values in column with UNIQUE index

问题描述

我们有一个表，它在可以接受空值的列上有一个唯一索引.问题是我们发现这个结构只能接受 NULL 值的一行.如果我们尝试添加带有 NULL 值的第二行，我们会收到类似的错误.不能在对象中插入重复的关键行......"

We have a table that has a unique index on a column that can accept null values. Problem is that we found out this structure can only accept one row with NULL value. If we try to add second row with NULL value we get an error like. "can not insert duplicate key row in object…."

我们可以做些什么来保持此列上的索引以及向多行添加 NULL 值的能力?

Is there anything we can do to keep the index on this column and the ability to add NULL values to more than one row?

推荐答案

是的，您可以使用过滤索引来支持这一点.只需删除现有索引并像这样创建新索引

Yes, you can use filtered index to support this. Just drop your existing index and create new index like this

CREATE UNIQUE INDEX Index_Name ON TableName(ColumnName)
WHERE ColumnName IS NOT NULL

这将允许您拥有重复的 NULL 值.如果您需要更多详细信息，这里有一篇深入的文章.

This will allow you to have duplicates of NULL values. Here is an in depth article if you need more details on this.

http://blog.sqlauthority.com/2008/09/01/sql-server-2008-introduction-to-filtered-index-improve-performance-with-filtered-index/

这篇关于具有 UNIQUE 索引的列中的多个 NULL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！