• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

DUPLICATE KEYS IN AN EXISTING UNIQUE INDEX

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
leorocha
Asked:
leorocha
  • 3
  • 2
  • 2
  • +3
1 Solution
 
Anthony PerkinsCommented:
>>How can I fix this problemn and rebuild the PK index?<<
Delete the duplcate rows.
0
 
Kevin3NFCommented:
Fix the data....you've got duplicates...look for 3301 in the relevant field.
0
 
Brian CroweDatabase AdministratorCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony PerkinsCommented:
BriCrowe,

I suspect that is the problem:  The questioner does not have a valid "PKField".
0
 
Scott PletcherSenior DBACommented:
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
 
Anthony PerkinsCommented:
Thanks Scott.  Good to know.
0
 
Eugene ZCommented:
?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
 
roshkmCommented:
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
 
roshkmCommented:
After that u can restore the PK.

RKM
0
 
Scott PletcherSenior DBACommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now