CODEWITHSUNDEEP
sqlsql-servert-sql
satyajit
satyajit

Reputation: 2700

Enforcing unique and multiple NULLS for a column in a table

How do I enforce Unique values as well as multiple NULLS for a column of a table in SQL Server?

Upvotes: 2

Views: 457

Answers (2)

gbn
gbn

Reputation: 432200

Other answers mention filtered indexes and triggers.

Filtered indexes are SQL Server 2008 only. For earlier versions and to avoid code (trigger):

  • Either you can use an indexed view with a IS NOT NULL filter. This is a DRI approach

  • Or you can have a computed column with ISNULL(TheColumn, -PKIdentityCol) (or some other value based on the PK) which is also DRI

Upvotes: 3

Matt Ball
Matt Ball

Reputation: 359776

From this answer:

In SQL Server 2008, you can define a unique filtered index based on a predicate that excludes NULLs:

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

In earlier versions, you can resort to VIEWS with a NOT NULL predicate to enforce the constraint.

Upvotes: 4

Related Questions