CODEWITHSUNDEEP
sql-serversql-server-2008
Cartesius00
Cartesius00

Reputation: 24414

Unique constraint only over NON-NULL columns

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.

Upvotes: 5

Views: 3774

Answers (1)

Akram Shahda
Akram Shahda

Reputation: 14781

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.

Upvotes: 7

Related Questions