Enforcing unique and multiple NULLS for a column in a table

开发者_JAVA技巧

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

---

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.

---

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