Search code examples
sqlsql-serversql-server-2008nullunique

unique constraint with a condition to accept multiple nulls on a unique column

I was wondering what would the proper way to have a Unique conditional constraint be? So if a user enters nothing into the AccountIdentifier field and submit, it will show up as Null in the database, however because of the unique constraint, it can't have multiple nulls, is there a way to do this?

CREATE TABLE [dbo].[AccountTemplate]
(
    [AccountID] INT NOT NULL,
    [AccountIdentifier] NCHAR(50) NULL,
    CONSTRAINT [FK_AccountTemplate_Identifier] UNIQUE NONCLUSTERED ([AccountIdentifier] ASC, [AccountID] ASC)
)
Solution

  • Create your index as filtered. If you leave out null values from the index, the uniqueness will not be enforced either.