I'm having a problem maintaining data integrity on a database table in MS SQL-Server 2000. To ensure maintaining the integrity I need to implement a Unique Constraint on one of my coloumns. The problem however is, that this coloumn also has to allow multiple NULL-values - but for every value in the coloumn not being NULL, the values has to be unique.
I have tried with the following code, and it almost works:
ALTER TABLE dbo.Table1 WITH NOCHECK ADD CONSTRAINT
CK_Table1 CHECK (
Col1 is null or
Col1 not in (Table1.Col1)
The idea was to implement a check constraint, that for every data-change on the column would check to see, if the Col1 value is either being NULL or a non-existing value in the very same column.
However the last part ( Col1 not in (Table1.Col1) ) does not seem to work, and also I have a doubt, that the constraint would accept data-changes on another coloumn, since Col1 would then be compared to its own value - that being a value already in the table, the constraint might also fail due to that.
Does anyone have an idea how to come about this?
Alternative data model design suggestions are of course also welcome!