Post reply

Violation of … constraint ‘…’. Cannot insert duplicate key in object ‘…’

  • October 10, 2019 at 7:24 pm

    #3687844

    Hi, I am getting the error message

    Violation of … constraint ‘…’. Cannot insert duplicate key in object ‘…’ (Msg 2627)

    when I try to insert records into a table using MERGE command.

    Actually, the target table which I try to insert records is empty & does not have any records now.

    Please tell me why I am getting this error message as no existing records are there, there by preventing duplicate insertion.

  • October 10, 2019 at 7:37 pm

    #3687845

    It sounds like the query you are running to merge into the table is returning duplicate rows.  The merge statement requires each row to be unique.

  • October 11, 2019 at 3:27 am

    #3687924

    Just think about the logic for a minute.. I have a target table (seats) and a source table (passengers). I can only put one passenger in one seat. That's what the error message is is telling you. This is why we use keys in SQL. However as a warning don't ever use the old UPDATE.. FROM.. Sybase syntax. Based on physical storage, instead of a logical model, this will grab the last matching row from the source and use it ignoring all others. Essentially meanest nasties passenger was able to bully everybody else out of that seat. You don't get an error message or even a warning, so the cardinality error destroys your data integrity.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • October 11, 2019 at 4:39 am

    #3687925

    Thanks for the insight. The source query was returning duplicate records. I will use DISTINCT clause & see the output.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply