Unique constraint only over NON-NULL columns

Question

In MS SQL Server 2008, is it possible to define UNIQUE CONSTRAINT on a column BUT which is checked IF and ONLY IF for NON-NULL values. That is, there will be many NULLs which should be ignored. For non-nulls it should be unique, however.

possible duplicate of [SQL Server UNIQUE constraint with duplicate NULLs](http://stackoverflow.com/questions/1796414/sql-server-unique-constraint-with-duplicate-nulls) (and plenty of others) — Andriy M, Jun 18 '11 at 08:32

score 6 · Accepted Answer · answered Jun 18 '11 at 08:29

6

You need to create a Filtered Index:

CREATE UNIQUE NONCLUSTERED INDEX User_PinNr_IUC
ON [User] (pinNr)
WHERE pinNr IS NOT NULL

Refering to CREATE INDEX (Transact-SQL)

WHERE < filter_predicate > :
Creates a filtered index by specifying which rows to include in the index. The filtered index must be a nonclustered index on a table. Creates filtered statistics for the data rows in the filtered index.

answered Jun 18 '11 at 08:29

Akram Shahda

14,655
4
45
65

Thank you! Is there any difference between CREATE INDEX and ADD CONSTRAINT? And please, how to write a condition for varchar NULL or EMPTY/WHITESPACE? – Cartesius00 Jun 18 '11 at 08:34
@James: CREATE INDEX is an independent statement. ADD CONSTRAINT used in CREATE/ALTER TABLE statements. – Akram Shahda Jun 18 '11 at 08:40
@James: VARCHARColumn is null OR VARCHARColumn = "" – Akram Shahda Jun 18 '11 at 08:41

Unique constraint only over NON-NULL columns

1 Answers1

Linked