MS SQL 2000 Uniqute Constraint

[KrisSimonis]

Ok, MS SQL 2000 is playing silly buggers with me.. I have a table, which has a column (type varchar(10)) that can contain <NULL> values.

When I try to create a UNIQUE constraint on this column, I get an error that duplicate keys are found. Namely <NULL>. But according to MSDN, a UNIQUE constraint is supposed to ignore <NULL> values.

Aside from the numerous <NULL> values in this column, I am 100% certain that there are no other duplicates in this column.

Am I doing something wrong, or is the documentation wrong?
Or is this just MSSQL having a bad day and taking it out on me?

[davide++]

Hi all.

In fact unique constraints on a field ignore NULL value and fire when you insert duplicated not null values;
You should check that there aren't duplicated not null values using a query like this:


SELECT UNIQUE_FIELD, COUNT(UNIQUE_FIELD)
FROM MYTABLE
WHERE UNIQUE_FIELD IS NOT NULL
GROUP BY UNIQUE_FIELD


Otherwise I think this's MS SQL 2000's bug (so hard to say...)

[KrisSimonis]

I checked that, and wrote just such a query: :cool:

SELECT CustomerID,
MembershipCode
FROM Customers C1
WHERE MembershipCode IS NOT NULL
AND ((SELECT COUNT(*)
FROM Customers C2
WHERE C2.MembershipCode = C1.MembershipCode) > 1)


MembershipCode is the field that has to become unique and is giving me hell. CustomerID is our Identity/PrimaryKey column. These MembershipCodes are generated by some external application and are typed in by hand when they are adding customers. :sick:

I've done some research myself on it, and found that MS SQL 2000 indeed cannot handle NULL values in a unique column. It treats these as normal data, so it will generate the duplicate key error if there's more than 1 present. And thus, another kitten drowns. :cry:

I built a little workaround for it. Since there's only one place in our application where these numbers can be entered into our database, I put a little check there to see if the number entered is already in use. And spanks the user if it is. :D