Unique Constraint not working

[mague4718]

I put a constraint on a table so that the database only accepts unique Social security numbers. The problem is that If I have a <NULL> value in the database I cannot insert another <NULL> value. I thought that this UNIQUE contraint ignored <NULL>'s. Any ideas??
This is how I created the contraint:

ALTER TABLE Customers
ADD CONSTRAINT UQ_SSN UNIQUE NONCLUSTERED (SSN)
GO

 

[Guest_imported]

Null is a value, albeit an undefined one.

Any unique constraint on a single column will allow one and only one null providing the column allows nulls.

To handle a case where null social security numbers are ok but identical social security numbers are not, you will have to write this as a trigger instead.

 

[bperry]

Hi there.
It is well known that in SQL Server, Unique constraints will only allow 1 NULL value. That's just the way it is.

There are a few workarounds that people have developed. Here are two of them.....I recommend the first one (because I don't really understand the second one!)

http://www3.ns.sympatico.ca/brianperry/sqlnotes/sqlexam28.html

rgrds, etc
brian perry

 

[bperry]

Ooops, almost forgot.
Instead of a unique constraint, another way (probably the most common) is to implement the integrity check you need with a trigger on the table in question. The trigger would check for unique values (rolling back if the new entry was not unique), but would allow more than i NULL.

My apologies for not suggesting this solution earlier.

rgrds, etc
brian perry