locked
SQL Server violatio of Primary Key RRS feed

  • Question

  • Hi Techies,

    I am trying to update some data in my SQL base software.

    Its giving me error

    Sorry(Microsoft)(ODBCSQL Server Driver)(SQL Server) violation of primary key constraint 'PK _Headmaster'. Cannot insert duplicate key in object 'Headmaster'

    The file is in XLS format and it shows me no duplicates.

    Seeking urgent advice.

    Thursday, October 7, 2010 6:25 AM

Answers

  • Thanks to all for kind advice.

    I myself found out the solution...actually i was missing something before uploading the file. I deleted all the previous data after taking backup and imported the new file.

    Its done.

    Thanks again to all.

    • Marked as answer by RA.ONE Monday, October 11, 2010 5:58 AM
    Monday, October 11, 2010 5:58 AM

All replies

  • Check the on the column defined as Primary Key.

    Check the field lenght and the value on the excel file to check against if they are unique.

    See if the collation of the Primary Key column, if it is case senisitive

     

     


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Thursday, October 7, 2010 7:45 AM
  • Thanks for the Post.

    Case sensitive is not a problem.

    Field length..m not sure.......n i didnt find any duplicates too.........

    Thursday, October 7, 2010 8:06 AM
  • Is there a possibility that blank string is also getting imported and its putting two blank string in PK column? Profiler trace would help.

    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    • Proposed as answer by Kalman Toth Friday, October 8, 2010 6:47 AM
    Thursday, October 7, 2010 8:35 AM
  • Thanks for the post.

    Sir, the problem is i am a fresher for SQL..

    I got one file to update in the software...I deleted the duplicates, trim the names, concat the rows n columns..

    I opened my software imported the file, software searched the file and imported successful. But when i click the transfer it gives this error......

    I dont know whether the problem is in my XLS file or in the software.

    Thursday, October 7, 2010 10:02 AM
  • Try to temporary drop primary key constraint, import the data and run the following statement (obviously you need to change PK column name)

    select PkField, count(*)
    from Headmaster
    group by PKField
    having count(*) > 1
    
    It will give you a list of the duplicates. Next, fix the problem and recreate PK constraint. You can script drop/create in the management studio


    Thank you!

    My blog: http://aboutsqlserver.com

    Thursday, October 7, 2010 5:40 PM
  • Import the new file into a staging table. You can then use the INTERSECT operator to identify conflicting Primary Keys. Example follows for INTERSECT:

    USE tempdb;
    SELECT TOP(10) * INTO Product FROM AdventureWorks2008.Production.Product ORDER BY NEWID()
    -- (10 row(s) affected)
    
    SELECT ProductID FROM AdventureWorks2008.Production.Product
    INTERSECT
    SELECT ProductID FROM Product

    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Friday, October 8, 2010 6:51 AM
  • Thanks to all for kind advice.

    I myself found out the solution...actually i was missing something before uploading the file. I deleted all the previous data after taking backup and imported the new file.

    Its done.

    Thanks again to all.

    • Marked as answer by RA.ONE Monday, October 11, 2010 5:58 AM
    Monday, October 11, 2010 5:58 AM