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

user_80385 0
Viewed 310642

I want to have a unique constraint on a column which I am going to populate with GUIDs. However, my data contains null values for this columns. How do I create the constraint that allows multiple null values?

Here's an example scenario. Consider this schema:

CREATE TABLE People (
  Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,
  Name NVARCHAR(250) NOT NULL,
  LibraryCardId UNIQUEIDENTIFIER NULL,
  CONSTRAINT UQ_People_LibraryCardId UNIQUE (LibraryCardId)
)

Then see this code for what I'm trying to achieve:

-- This works fine:
INSERT INTO People (Name, LibraryCardId) 
 VALUES ('John Doe', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');

-- This also works fine, obviously:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Marie Doe', 'BBBBBBBB-BBBB-BBBB-BBBB-BBBBBBBBBBBB');

-- This would *correctly* fail:
--INSERT INTO People (Name, LibraryCardId) 
--VALUES ('John Doe the Second', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');

-- This works fine this one first time:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Richard Roe', NULL);

-- THE PROBLEM: This fails even though I'd like to be able to do this:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Marcus Roe', NULL);

The final statement fails with a message:

Violation of UNIQUE KEY constraint 'UQ_People_LibraryCardId'. Cannot insert duplicate key in object 'dbo.People'.

How can I change my schema and/or uniqueness constraint so that it allows multiple NULL values, while still checking for uniqueness on actual data?

tsql sql-server
15 Answers

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.

user_903520

SQL Server 2008 +

You can create a unique index that accept multiple NULLs with a WHERE clause. See the answer below.

Prior to SQL Server 2008

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.

user_836670

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
user_551590

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)
user_525980

In my experience - if you're thinking a column needs to allow NULLs but also needs to be UNIQUE for values where they exist, you may be modelling the data incorrectly. This often suggests you're creating a separate sub-entity within the same table as a different entity. It probably makes more sense to have this entity in a second table.

In the provided example, I would put LibraryCardId in a separate LibraryCards table with a unique not-null foreign key to the People table:

CREATE TABLE People (
  Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,
  Name NVARCHAR(250) NOT NULL,
)
CREATE TABLE LibraryCards (    
  LibraryCardId UNIQUEIDENTIFIER CONSTRAINT PK_LibraryCards PRIMARY KEY,
  PersonId INT NOT NULL
  CONSTRAINT UQ_LibraryCardId_PersonId UNIQUE (PersonId),
  FOREIGN KEY (PersonId) REFERENCES People(id)
)

This way you don't need to bother with a column being both unique and nullable. If a person doesn't have a library card, they just won't have a record in the library cards table. Also, if there are additional attributes about the library card (perhaps Expiration Date or something), you now have a logical place to put those fields.

user_154793450

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.

Marc_Gravell_233540

You can create an INSTEAD OF trigger to check for specific conditions and error if they are met. Creating an index can be costly on larger tables.

Here's an example:

CREATE TRIGGER PONY.trg_pony_unique_name ON PONY.tbl_pony
 INSTEAD OF INSERT, UPDATE
 AS
BEGIN
 IF EXISTS(
    SELECT TOP (1) 1 
    FROM inserted i
    GROUP BY i.pony_name
    HAVING COUNT(1) > 1     
    ) 
     OR EXISTS(
    SELECT TOP (1) 1 
    FROM PONY.tbl_pony t
    INNER JOIN inserted i
    ON i.pony_name = t.pony_name
    )
    THROW 911911, 'A pony must have a name as unique as s/he is. --PAS', 16;
 ELSE
    INSERT INTO PONY.tbl_pony (pony_name, stable_id, pet_human_id)
    SELECT pony_name, stable_id, pet_human_id
    FROM inserted
 END
user_21095120
Related