How do I create unique constraint that also allows nulls in sql server

Question

The question is quite simple, as the title. Added a column to table I want a unique constraint , the column will insert guids, but to allow for existing data before column was added I need to allow for nulls in the check

Answer 1

Create a view that selects only non-NULL columns and create the UNIQUE INDEX on the view:

CREATE VIEW myview
AS
SELECT  *
FROM    mytable
WHERE   mycolumn IS NOT NULL

CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)

Note that you'll need to perform INSERT's and UPDATE's on the view instead of table.

You may do it with an INSTEAD OF trigger:

CREATE TRIGGER trg_mytable_insert ON mytable
INSTEAD OF INSERT
AS
BEGIN
        INSERT
        INTO    myview
        SELECT  *
        FROM    inserted
END

Answer 2

You cannot create a UNIQUE constraint and allow NULLs. You need set a default value of NEWID().

Update the existing values to NEWID() where NULL before creating the UNIQUE constraint.

Answer 3

It is possible to create a unique constraint on a Clustered Indexed View

You can create the View like this:

CREATE VIEW dbo.VIEW_OfYourTable WITH SCHEMABINDING AS
SELECT YourUniqueColumnWithNullValues FROM dbo.YourTable
WHERE YourUniqueColumnWithNullValues IS NOT NULL;

---

and the unique constraint like this:

CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_OFYOURTABLE 
  ON dbo.VIEW_OfYourTable(YourUniqueColumnWithNullValues)

Answer 4

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.

Answer 5

Maybe consider an "INSTEAD OF" trigger and do the check yourself? With a non-clustered (non-unique) index on the column to enable the lookup.