34

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?)

Community
  • 1
  • 1
Patonza
  • 6,557
  • 5
  • 25
  • 20

6 Answers6

57

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
cuongle
  • 74,024
  • 28
  • 151
  • 206
LorenVS
  • 12,597
  • 10
  • 47
  • 54
4

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
Community
  • 1
  • 1
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • Looks like much the same, wasn't able to find that question before posting. Anyway, here we got a few nice answer about TSQL2008 "filtered indexes" (which I didn't even know existed), so I guess it was worth the duplication :) – Patonza Nov 25 '09 at 12:57
  • That may lead to non-unique values - consider where `dupNulls.pk = 1, X = null && dupNulls.pk = 2, X = 2` – Rowland Shaw Sep 02 '10 at 15:42
  • @Rowland Shaw: the values used for the dupNulls_uqX UNIQUE constraint will be `(NULL, 1) && (2, 0)`. I see no duplicate value. – onedaywhen Sep 06 '10 at 07:55
3

If you're using SQL Server 2008, have a look into Filtered Indexes to achieve what you want.

For older version of SQL Server, a possible alternative to a trigger involves a computed column:

  1. Create a computed column which uses the value of your "unique" column if it's not NULL, otherwise it uses the value of the row's Primary Key column (or any column which will be unique).
  2. Apply a UNIQUE constraint to the computed column.
Paul Turner
  • 38,949
  • 15
  • 102
  • 166
  • Very interesting trick the use of a computed column. There could be a collision problem between the PK and the actual field value, but with some prefixing it should work. – Patonza Nov 25 '09 at 12:50
2

http://www.sqlmag.com/article/articleid/98678/sql_server_blog_98678.html

will work only in Microsoft SQL Server 2008
1

You can create a view in which you select only not null values and create an index on it.

Here is the source - Creating Indexed Views

Svetlozar Angelov
  • 21,214
  • 6
  • 62
  • 67
0

You should use UNIQUEIDENTIFIER in that column, can be NULL and also is unique by definition. Hope that helps.

pablox
  • 643
  • 2
  • 8
  • 17