开发者

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

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜