2

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

satyajit
  • 2,540
  • 11
  • 33
  • 44
  • 1
    Meaning, only non-null values must be unique? Not identical, but related: http://stackoverflow.com/questions/191421/how-to-create-a-unique-index-on-a-null-column – Matt Ball May 15 '11 at 18:59
  • I don't understand your question. Could you clarify? – Ash Burlaczenko May 15 '11 at 19:00
  • @ Ash Burlaczenko I want a column to accept only unique values but we should be able to insert any number of null values.In sql server If you go for UIQUE constraint, then that allows only 1 NULL value.But I require multiple null values – satyajit May 15 '11 at 19:07

2 Answers2

4

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.

Community
  • 1
  • 1
Matt Ball
  • 354,903
  • 100
  • 647
  • 710
3

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • Forgive me for being dense, but what is DRI? – Cheran Shunmugavel May 16 '11 at 06:03
  • @Cheran S: [Declarative Referential Integrity](http://en.wikipedia.org/wiki/Declarative_Referential_Integrity): primary keys, foreign keys, unique/check/default/NULL/constraints etc – gbn May 16 '11 at 06:12