With SQL Server I want a unique index on only the non-null values

Question

Possible Duplicate:
SQL Server UNIQUE constraint with duplicate NULLs

In Sql Server I would like to have a column that has both nulls and real values. If the column has a value, I want to insure it is unique. The column could have multiple rows with null for this column.

For example suppose I had a table of books with 3 columns, namely:

Book Number - identity
Title - varchar
ISBN - char

Now the table would have a primary key with the identity column, which is good. The question would be on the ISBN column. I do not want any ISBN number to appear in the table more than once, but there will be many books with no ISBN number (since I don't know it).

Is there any way (or what is the best way) to inforce this constraint on ISBN?

@astander: which is itself a duplicate of http://stackoverflow.com/questions/191421/how-to-create-a-unique-index-on-a-null-column which duplicates many others. let's just answer the question? :) — Andomar, Jan 13 '11 at 06:31
@Andomar, which then defeats the purpose of *close->Duplicate question* — Adriaan Stander, Jan 13 '11 at 06:51
@astander: Perhaps 0.001% of duplicates is actually closed as duplicate. And some of those that get closed are not real duplicates. — Andomar, Jan 13 '11 at 07:26
I agree, this is answered previously in the referenced question, sorry. — JonnyBoats, Jan 13 '11 at 14:44
@astander: See http://stackoverflow.com/questions/4680867/group-by-date-confirm-pending. No close votes, nice answer, overall the goodness of stackoverflow-- but definitely a duplicate. — Andomar, Jan 13 '11 at 15:04

score 9 · Accepted Answer · edited May 23 '17 at 12:02

In SQL Server 2008, you can create a filtered index, like:

CREATE UNIQUE INDEX indexName ON tableName(isbn) WHERE isbn IS NOT NULL

In earlier versions, create a calculated column which is the ISBN when it exists, and the primary key when the ISBN is null:

CREATE TABLE tableName (
    pk int identity(1,1) primary key,
    isbn int NULL,
    nullbuster as (case when isbn is null then pk else 0 end),
    CONSTRAINT dupNulls_uqX UNIQUE (isbn)
)

Copied from SQL Server UNIQUE constraint with duplicate NULLs, so this is in wiki mode :)

With SQL Server I want a unique index on only the non-null values

1 Answers1

Linked