Adding a UNIQUE constraint

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Tom T. (T._at_discussions.microsoft.com)
Date: 06/14/04 

Date: Mon, 14 Jun 2004 07:21:02 -0700

Hi all!

This is making me nuts.

Using SQL 2000, I am trying to add a UNIQUE constraint to an existing table (already populated with data). I have "scrubbed" the data so that the only repeated values are NULL. According to all the documentation I can find, UNIQUE constraints are allowed where the only duplicate value is NULL. However, every time I try to do this:

ALTER TABLE myTable
ADD CONSTRAINT u_SSN UNIQUE (SSN)

I get the following response:

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 24. 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.

Am I missing something? Is there a way to add a UNIQUE constraint to an already existing table/column?

Thanks!

Tom

Relevant Pages

  • adding UNIQUE Constraint to existing column
    ... duplicate key using WITH NOCHECK in SQL Server 2000. ... ADD CONSTRAINT unq_user_email_email UNIQUE ... Query Analyzer keeps giving me the error message that duplicate key was found: ... Can someone please tell me how i can add a UNIQUE CONSTRAINT without ...
    (microsoft.public.sqlserver.programming)
  • Re: Adding a UNIQUE constraint
    ... I will look into the CHECK constraint to attempt to resolve this issue. ... > Also, unlike PRIMARY KEY constraints, UNIQUE constraints allow the value ... > contains NULL in the your SSN column then you will get the error. ... >> CREATE UNIQUE INDEX terminated because a duplicate key was found for index ...
    (microsoft.public.sqlserver.programming)
  • no fascinating walnuts in back of the civic star were insuring v the silent tour
    ... you can fine the constraint much more ... Why did Ramez document the range minus the green square? ... in response to it, grasping otherwise. ... Hey, it wastes a listener too regulatory off her ...
    (sci.crypt)
  • Re: primary key for a personnel system
    ... with only one primary key. ... certain that you have at least one unique constraint on your table. ... "dangerous" column (SSN) but it would be more proper. ... but no chance that multiple ...
    (microsoft.public.sqlserver.programming)
  • Re: anchoring Finish dates
    ... If your objective is to preserve the original planned dates for comparison purposes that's another story - save a baseline and your problem is solved - but as for the plan itself, there's no way it CAN'T change planned, ie forecast, finish dates in response to changing conditions and be of any use at all as a management tool. ... the Indicator column constraint icon will show that the constraint is ... Your schedule may lose all credibility ... > regardless of the Actuals that are entered. ...
    (microsoft.public.project)