none
CREATE UNIQUE INDEX statement terminated because a duplicate key was found

    Question

  • hello experts,
    i am getting following error
    Msg 1505, Level 16, State 1, Procedure spUtil_ReIndexDatabase_UpdateStats, Line 15
    The CREATE UNIQUE INDEX statement terminated because a duplicate key was found 
    for the object name 'dbo.bill' 
    and the index name 'PK_bill'. The duplicate key value is (1f05c0c8-c539-438c-a787-8830afdca891).
    i tried 
    SELECT ui, finyear,plantID,doc_gl,connect2,doc_no,doc_dt,log_date
    FROM bill
    where ui='1f05c0c8-c539-438c-a787-8830afdca891'
    it is returning one row only
    [bill].[dbo].[ ui] column is primary key
    finyear,plantID,connect2,doc_no of the same bill table have unique index
    what went wrong
    how to handle this problem,
    kindly help
    Sunday, December 11, 2011 4:43 PM

Answers

  • I got the mistake,

    i deleted primary key and unique index from bill  table then 

    SELECT plantid,finyear,connect2,doc_no,duplicate=count(*)
    INTO holdkey
    FROM bill
    GROUP BY plantid,finyear,connect2,doc_no
    HAVING count(*) > 1
    				
    SELECT DISTINCT a.*
    INTO holddups
    FROM bill a, holdkey b
    WHERE a.plantID = b.plantID
    AND a.finyear=b.finyear
    and a.connect2=b.connect2
    and a.doc_no=b.doc_no
    
    DELETE bill
    FROM bill a,holdkey b
    WHERE a.plantID = b.plantID
    AND a.finyear=b.finyear
    and a.connect2=b.connect2
    and a.doc_no=b.doc_no		
    
    INSERT bill SELECT * FROM holddups		
    

     

    this resolved the problem

    thanks for everybody, who spent time to help me out,

    also thanks to writers of http://support.microsoft.com/kb/290917 article


    • Marked as answer by Sushil Agarwal Monday, December 12, 2011 10:55 AM
    • Edited by Sushil Agarwal Wednesday, December 14, 2011 5:07 AM Higlighting REal problem sentence
    Monday, December 12, 2011 10:55 AM

All replies

  • If you showed the Create statement it would be easier.  I can only say that the duplication is out there.  Just remember to look for the duplicate data in the column or columns included in the Create statement (and this is why it would be good if you showed it, to see the columns involved).
    Jose R. MCP
    Sunday, December 11, 2011 4:47 PM
  • dear webjose,

     

    i am no getting what do you mean by create statment.

    i me issuing 

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[spUtil_ReIndexDatabase_UpdateStats]
    AS
    DECLARE @MyTable VARCHAR(255)
    DECLARE myCursor
    CURSOR FOR
    SELECT TABLE_SCHEMA+'.'+table_name as table_name 
    FROM information_schema.tables
    WHERE table_type = 'base table'
    OPEN myCursor
    FETCH NEXT 
    FROM myCursor INTO @MyTable
    WHILE @@FETCH_STATUS = 0
    BEGIN
    PRINT 'Reindexing Table:  ' + @MyTable
    DBCC DBREINDEX(@MyTable, '', 80)
    FETCH NEXT
    FROM myCursor INTO @MyTable
    END
    CLOSE myCursor
    DEALLOCATE myCursor
    

    then i got the above error, i am suspecting bill table to have some trouble, the user was not seeing a record, 

    some time it show the record the other time is is missing, so i decided to re-index and got the error.

    i understand there is conflict , but how to locate it

    Sunday, December 11, 2011 4:56 PM
  • Ah, I see.  Yes, examine the bill table.  Discover any and all unique indexes in this table, and then start searching for duplicates in the columns listed in the unique indexes, and then remove the duplication.
    Jose R. MCP
    Sunday, December 11, 2011 5:06 PM
  • i have pk on ui column

    select ui,COUNT(*) [count]
    from bill
    group by ui
    having COUNT(*) > 1
    
    

    return 0 rows

    unique index ix_bill on plantID,finyear,connect2,doc_no
    select plantID,finyear,connect2,doc_no,COUNT(*) 
    from bill
    group by plantID,finyear,connect2,doc_no
    having COUNT(*) > 1
    
    
    return 0 rows
    how to correct the issues
    only above two  index are there for the table ?

     

     

    Sunday, December 11, 2011 5:15 PM
  • I got the mistake,

    i deleted primary key and unique index from bill  table then 

    SELECT plantid,finyear,connect2,doc_no,duplicate=count(*)
    INTO holdkey
    FROM bill
    GROUP BY plantid,finyear,connect2,doc_no
    HAVING count(*) > 1
    				
    SELECT DISTINCT a.*
    INTO holddups
    FROM bill a, holdkey b
    WHERE a.plantID = b.plantID
    AND a.finyear=b.finyear
    and a.connect2=b.connect2
    and a.doc_no=b.doc_no
    
    DELETE bill
    FROM bill a,holdkey b
    WHERE a.plantID = b.plantID
    AND a.finyear=b.finyear
    and a.connect2=b.connect2
    and a.doc_no=b.doc_no		
    
    INSERT bill SELECT * FROM holddups		
    

     

    this resolved the problem

    thanks for everybody, who spent time to help me out,

    also thanks to writers of http://support.microsoft.com/kb/290917 article


    • Marked as answer by Sushil Agarwal Monday, December 12, 2011 10:55 AM
    • Edited by Sushil Agarwal Wednesday, December 14, 2011 5:07 AM Higlighting REal problem sentence
    Monday, December 12, 2011 10:55 AM
  • Hi Sushil,
    I’m glad to hear that you have resolved the problem and thank you for sharing the solution with us.
    In addition, I suggest you to create thread in the Transact-SQL Forum if you meet similar questions.
    Have a nice day.

    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, December 13, 2011 2:24 AM