Menu
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….”
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?
851
Therefore, if a unique index consists of a single column, only one null value is allowed-more than one null value would violate the unique constraint. Similarly, if a unique index consists of multiple columns, a specific combination of values and nulls can be used only one time.
A unique index never has duplicate values.
You can prevent duplicate values in a field in an Access table by creating a unique index.
PRIMARY KEY constraint differs from the UNIQUE constraint in that; you can create multiple UNIQUE constraints in a table, with the ability to define only one SQL PRIMARY KEY per each table. Another difference is that the UNIQUE constraint allows for one NULL value, but the PRIMARY KEY does not allow NULL values.
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
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/
181
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
