?
Solved

DUPLICATE KEYS IN AN EXISTING UNIQUE INDEX

Posted on 2005-03-02
10
Medium Priority
?
393 Views
Last Modified: 2008-02-01
I've a problem with a table of a sql 2000 server database. Nobody know how, but the table now has duplicate key values for an existing unique index (PK__.....). Then when I try to rebuild this index I get the following message:

Server: Msg 1505, Level 16, State 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '3301'.

How can I fix this problemn and rebuild the PK index?

Thanks,
    Leonel
0
Comment
Question by:leorocha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13443784
>>How can I fix this problemn and rebuild the PK index?<<
Delete the duplcate rows.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13443791
Fix the data....you've got duplicates...look for 3301 in the relevant field.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13443816
you can find the records with duplicate keys by running the query

SELECT * FROM mytable
WHERE PKField IN (SELECT T2.PKField FROM myTable T2 GROUP BY T2.PKField HAVING Count(*) > 1)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13443841
BriCrowe,

I suspect that is the problem:  The questioner does not have a valid "PKField".
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 13445148
If the original index was defined as a PRIMARY KEY, but not explicilty defined as UNIQUE, SQL would automatically add a "uniquifier" (as MS docs call it) to distinguish like primary key values from each other.  That is, your data *can* have non-unique primary keys and SQL will generate a value to make it unique.

Unfortunately, then when you try to explicitly specify UNIQUE, you get an error from the duplicate(s) in your data.  As BriCrowe noted, you can use a query to find the duplicate rows and then delete/update those that are causing problems.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13445340
Thanks Scott.  Good to know.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 13445795
?ScottPletcher

>If the original index was defined as a PRIMARY KEY, but not explicilty defined as UNIQUE, SQL would automatically add a "uniquifier" (as MS docs call it) to distinguish like primary key values from each other.

What do you mean? can you point to link? -> <as MS docs call it>

read:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_0wab.asp
SQL Server automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not already exist on the table, or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.

-------------------------
leorocha:
if you do not have latest SP3a please install
see
FIX: Incorrect Identity Values After Failed Unique Index Creation
http://support.microsoft.com/kb/290917/EN-US/
----------------------
if not :- try to search dups in PK field(s) and fix them

------------
If not:

Tell please more about what did you do after PK was dropped and PK rebuild?
Did you insert some data? or what?
0
 
LVL 4

Accepted Solution

by:
roshkm earned 750 total points
ID: 13448805
Here is how u can delete duplicate entries:

DELETE FROM
     TABLE1 T1
INNER JOIN
     (
          SELECT
               MAX(PKCOLUMN) AS PKID, PKCol
          FROM
               TABLE
          GROUP BY     PKCol
          HAVING         COUNT(*) > 1
     ) T2
     ON(
          T1.PKCol= T2.PKCol
          AND
          T1.PKID<> T2.PKID
     )


Cheers,
RKM
0
 
LVL 4

Expert Comment

by:roshkm
ID: 13448812
After that u can restore the PK.

RKM
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 13449793
SORRY, it's only if you specify CLUSTERED but *not* PRIMARY KEY.  I often do that.  I only use PRIMARY KEY if I have to, for RI, because it seems to me to violate relational rules/theory.


DROP TABLE #temp
GO
CREATE TABLE #temp (
      c1 INT,
      c2 VARCHAR(10)
      )
CREATE CLUSTERED INDEX temp_clus
      ON #temp (c1)
GO
SET NOCOUNT ON
INSERT INTO #temp VALUES(1, 'A')
INSERT INTO #temp VALUES(1, 'B')
INSERT INTO #temp VALUES(1, 'C')
INSERT INTO #temp VALUES(1, 'D')
SET NOCOUNT OFF
GO
SELECT *
FROM #temp
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question