How do I create a unique constraint that also allows nulls?

by

What you’re looking for is indeed part of the ANSI standards SQL:92, SQL:1999 and SQL:2003, ie a UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values.

In the Microsoft world of SQL Server however, a single NULL is allowed but multiple NULLs are not…

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.

More Related Contents:

  1. How can I remove duplicate rows?
  2. Check if table exists in SQL Server
  3. Using T-SQL, return nth delimited element from a string
  4. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  5. What is the use of GO in SQL Server Management Studio & Transact SQL?
  6. SELECT DISTINCT on one column
  7. Using a if condition in an insert SQL Server
  8. Altering a column: null to not null
  9. Maximum size of a varchar(max) variable
  10. Confused about UPDLOCK, HOLDLOCK
  11. Is there a way to simplify a NULL compare of 2 values
  12. What is the maximum number of characters that nvarchar(MAX) will hold?
  13. TSQL Email Validation (without regex)
  14. Dynamic SQL results into temp table in SQL Stored procedure
  15. SQL Server Output Clause into a scalar variable
  16. Sequence as default value for a column
  17. Aggregate bitwise-OR in a subquery
  18. How do I flush the PRINT buffer in TSQL?
  19. TABLOCK vs TABLOCKX
  20. How to convert number of minutes to hh:mm format in TSQL?
  21. OR Operator Short-circuit in SQL Server
  22. How do I select last 5 rows in a table without sorting?
  23. SELECT INTO a table variable in T-SQL
  24. T-SQL query to show table definition?
  25. How can I insert binary file data into a binary SQL field using a simple insert statement?
  26. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  27. T-SQL XOR Operator
  28. Include missing months in Group By query
  29. What are row, page and table locks? And when they are acquired?
  30. Random Weighted Choice in T-SQL

Leave a Comment