Is it possible to add a Unique constraint or a Unique index on an existing table where the column I want the constraint on can contain Nulls?
I'm trying to achieve a constraint that allows Nulls but does not allow Non-Unique Non-Nulls.
I have tried the following
ALTER TABLE [table1] ADD
CONSTRAINT [uqc_col1] UNIQUE
and get the following error message
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 12. Most significant primary key is '<NULL>'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
I'm assuming I get this because of the Nulls in the column??? but the column I'm putting the constraint on is not the primary key (the PK is on a different column). I have also made sure that there are no duplicate non null values.