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

This question already has answers here: Closed 12 years ago.

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?

---

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