uniqe constraint on a nullable column

Question

happy new year, my first question on this website , help please

I have a table called dbo.Person it has a column called Person_NCID , this is a nullable column but I want to create a unique constraint on not null values. when I create a uniqe constraint now it returns an error as I have more than one null value in the column. should I use a user-defined function to create a unique constraint?

Answer 1

You need a filtered index on this column which will ignore any null values in your table.

Try something like

CREATE UNIQUE INDEX IX_Person_Person_NCID ON dbo.Person(Person_NCID)
WHERE Person_NCID IS NOT NULL

Which will in effect will create a Unique constraint on the non null values .

Answer 2

Create a unique filtered index (SQL Server 2008 onwards):

CREATE UNIQUE NONCLUSTERED INDEX NC_Person_Person_NCID
ON dbo.Person(Person_NCID)
WHERE Person_NCID IS NOT NULL;