Menu
Possible Duplicate:
How do I create unique constraint that also allows nulls in sql server
I have a table where I need to force a column to have unique values. This column must be nullable and by business logic multiple NULL values should be permitted, whereas other duplicate values are not.
SQL Server UNIQUE constraint is no good in this situation because it considers NULL as regular values, so it will reject duplicate NULLs.
Currently, value uniqueness is granted by the BLL so I'm not looking for a dirty hack to make it work. I just would like to know if there is a clean solution to enforce this constraint in the DB.
And yeah, I know I can write a trigger to do that: is a trigger the only solution? (or the best solution anyway?)
244
When two NULL values are different, why multiple NULL values are not allowed in a column defined as UNIQUE constraint. It is probably SQL Server's implementation of a UNIQUE index is different than other database programs and also to maintain data integrity.
As you know, when you create a UNIQUE constraint on a nullable column, SQL Server allows only one NULL value, thereby maintaining the UNIQUEness.
The solution to allow nulls in unique fields is create a unique filtered index excluding the nulls of the index, due to that the uniqueness of the nulls will not be validated and multiple rows with nulls will be accepted.
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.
If you're using SQL Server 2008 (won't work for earlier version) there is the concept of a filtered index. You can create the index on a filtered subset of the table.
CREATE UNIQUE INDEX indexName ON tableName(columns) INCLUDE includeColumns WHERE columnName IS NOT NULL Duplicate of this question?
The calculated column trick is widely known as a "nullbuster"; my notes credit Steve Kass:
CREATE TABLE dupNulls ( pk int identity(1,1) primary key, X int NULL, nullbuster as (case when X is null then pk else 0 end), CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster) ) Works on SQL Server 2000. You may need ARITHABORT on e.g.
ALTER DATABASE MyDatabase SET ARITHABORT ON 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
