MSSQL duplicate key row exception

cannot insert duplicate key row in object with unique index the duplicate key value is
attempt to insert duplicate key row in object with unique index
cannot insert duplicate key in object but there are no duplicates
cannot insert duplicate key in object sql
cannot insert duplicate key row in object with unique index the statement has been terminated
cannot insert duplicate key row in object with unique index entity framework
violation of primary key constraint when no duplicate exists
duplicate key sql

We have a rule engine that is categorizing articles. Currently there is an issue, where we get the following error:

Cannot insert duplicate key row in object 'dbo.article_category' with unique index 'IX_article_category_no_duplicates'. The duplicate key value is (123, 456). The statement has been terminated.

The creation of the entry in article_category happens in a stored procedure. The main part of the procedure is this:

MERGE article_category AS [target]
USING (
    SELECT articleId, @categoryId, @creator, @now, @ruleId, 2 
    FROM @articleIdList
) AS [source] (articleId, categoryId, creator, createDate, ruleId, assignmentTypeId)
    ON (
        target.articleId = source.articleId 
        AND target.categoryId= source.categoryId
    )
WHEN NOT MATCHED THEN
    INSERT (articleId, categoryId, creator, createDate, ruleId, assignmentTypeId)
    VALUES (source.articleId, source.categoryId, source.creator, source.createDate, source.ruleId, source.assignmentTypeId);

When two rules try to insert the same category for the same article, this exception above happens.

How is it possible to stop this from happening? I thought that using the merge statement instead of if not exists (...) should prevent this from happening?

Are there other possibilities to prevent this exception?

Thanks in advance

Independent from merge, if the data you are inserting/updating violates unique constraint, you can have this error on both update or insert.

the merge ... using does not handle Unique Index Constraints. Merge makes insertion if it does not match with the condition held on using statement, otherwise make update operation.

In your case,

target.articleId = source.articleId 
    AND target.categoryId= source.categoryId

does not guarantee uniqueness thus you are getting unique index contraint expception.

Is it possible to determine what caused a duplicate key, Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/ Msg 2601, Level 14, State 1, Line 5 Cannot insert duplicate key row in object 'dbo. The message of the exception object will contain the same error text� Added in 11.2, the ignore_row_on_dupkey_index hint silently ignores duplicate values: insert /*+ ignore_row_on_dupkey_index ( acct ( username ) ) */ into accounts acct ( username, given_name ) select username, given_name from accounts_stage; So if accounts_stage has duplicate usernames, this will add one of the rows and bypass the other.

Yes as you answered the not exists clause will help instead of merge, plus modify the stored proc in a way that any given time only one condition will have access to insert the row.. This is a classy case of dead lock

FIX: "Cannot insert duplicate key" error occurs when you update a , Cannot insert duplicate key row in object <table name> with unique index <index name>. Cumulative Update 11 for SQL Server 2012 SP1. When you insert a new row into a table if the row causes a duplicate in UNIQUE index or PRIMARY KEY, MySQL will issue an error. However, if you specify the ON DUPLICATE KEY UPDATEoption in the INSERTstatement, MySQL will update the existing row with the new values instead. The syntax of INSERT ON DUPLICATE KEY UPDATEstatement is as follows:

You need to use a SOURCE that doesn't have duplicates by your UNIQUE columns. You can use a ROW_NUMBER() with a PARTITION BY <unique column> and a posterior filter to remove potentially duplicates before the actual insert:

;WITH DuplicateRanking AS
(
    SELECT 
        articleId = articleId, 
        categoryId = @categoryId, 
        creator = @creator, 
        createDate = @now, 
        ruleId = @ruleId, 
        assignmentTypeId = 2 ,
        DuplicateRanking = ROW_NUMBER() OVER (
            PARTITION BY 
                articleId       -- Your unique columns here
            ORDER BY 
                (SELECT NULL))  -- Your desired order here (will determine which row gets inserted)
    FROM 
        @articleIdList  
)
MERGE article_category AS [target]
USING DuplicateRanking AS [source]
    ON (
        target.articleId = source.articleId AND 
        target.categoryId= source.categoryId
    )
WHEN NOT MATCHED AND [source].DuplicateRanking = 1 -- Only insert 1 row per unique column set 
    THEN
    INSERT (articleId, categoryId, creator, createDate, ruleId, assignmentTypeId)
    VALUES (source.articleId, source.categoryId, source.creator, source.createDate, source.ruleId, source.assignmentTypeId);

Cannot insert duplicate key row in object, SQL Server Error Messages - Msg 2601. Error Message Server: Msg 2601, Level 16, State 1, Line 1 Cannot insert duplicate key row in object '<Object Name>'� In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes. With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

IGNORE_DUP_KEY slower on clustered indexes, If that row encounters a duplicate key, another error is raised and As part of raising an exception, SQL Server has to collect some data that is� In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes. With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

[informatica][SQLServer JDBC Driver][SQLServer]Cannot insert , SQLIntegrityConstraintViolationException: [informatica][SQLServer JDBC Driver][ SQLServer]Cannot insert duplicate key row in object 'dbo. Msg 2601, Level 14, State 1, Line 2 Cannot insert duplicate key row in object 'dbo.Currency' with unique index 'IX_Currency_CurrencyCode'. As can be seen from the script, the source table ([dbo].[Currency_New]) contains duplicate entries for the Canadian Dollar currency (CAD).

How to troubleshoot Error 2601 Cannot insert duplicate key row in , The first step is to identify the sql statements causing the Exception. SQL Server Profiler can display the Exceptions. Once you've identified the sql� The issue is that inserting two rows which have equal keys according to the index named [MyTable_Key] causes the ADO.NET Entity Framework to throw an exception and the case is that the semantics of equality is not known or is hard to get to know as it for instance takes locales into account as e.g. the sequence of two letters "Aa" may be considered equal to the single letter "Å" when the Danish locale is in power.

Comments
  • Does @articleIdList have any rows which would cause the duplication? The merge clause is only checked once for the dataset, not one row at a time.
  • Not sure if it's a duplicate but Aaron Bertrand's answer to this post can help you understand and prevent the race-condition that happens when two inserts of the same key are executed simultaneously.
  • @Larnu the @articleIdList is unique per call of the stored procedure
  • with the "not exists" clause the duplicate error still happened :(
  • @xeraphim it's not possible to insert duplicates with this code unless you aren't correctly declaring the PARTITION BY column/values or there's another clause of the merge that does an update. Is your unique constraint on columns articleId and categoryId?
  • yes it's on articleId and categoryId. Can the problem not be that this procedure is executed multiple times in milliseconds?
  • The merge ensures that the comparison/join is done on the same transaction and scope as the operations it will do. If its failing due to other records then there must be records already inserted with these values even if this insert fails. Do you have other operations on the same transaction? Are you sure that this merge is causing the error?