Problem adding unique constraint

Question

Problem adding unique constraint

Olja Dimitrijevic

Ten Centuries

Points: 1010
More actions
January 19, 2003 at 4:18 pm

#79622

Hi,
I am having problems adding a unique constraint to a table. The unique column has one non null value and all nulls. When I tried adding the constraint I got the following error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 16. 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 tried dropping the column and then adding it with the constraint but that gave me the same error. The Index ID numbers above vary depending on when and how I try to add the constraint. The table in question has an identity column primary key.
Many thanks in advance.
Edited by - suaaddaa on 01/19/2003 4:19:20 PM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Unfortunately I think this is commonly misunderstood or the engine has never worked right if the few pieces in BOL that say NULL values are ok is right. But I have never been able to add a unique constraint to any column with more than one NULL no matter if ANSI NULL on or off.

gbn SSCertifiable Points: 6429 More actions · Answer 2

I've had to use triggers in the past to ensure domain integrity.

I agree with Antares, BOL are wrong on this.

Or I've just never figured out how to do it...

Cheers

gbn SSCertifiable Points: 6429 More actions · Answer 3

I've had to use triggers in the past to ensure domain integrity.

I agree with Antares, BOL are wrong on this.

Or I've just never figured out how to do it...

Cheers

tcartwright SSCertifiable Points: 5573 More actions · Answer 4

Sounds like you want a check constraint more than a unique constraint. You just don't want them entering the same entry twice, but the field can contain nulls? In that scenario a check constraint would work for you, or a trigger like gbn suggested.

Tim C.

//Will write code for food

One Windows to rule them all, One Windows to find them,

One Windows to bring them all and in the darkness bind them

In the Land of Microsoft where the Shadows lie.

Tim C //Will code for food