How to deal with Violation of PRIMARY KEY constraint error

Question

I am having a real problem with my stored procedure with this script:

    INSERT INTO #tr_TxnDetails       
    SELECT 
        b.pid,
        b.etc
    FROM tbl_SomeTableA as a
    JOIN tbl_SomeTableB as b ON a.etc = b.etc 
        AND a.SomeColumn = b.SomeColumn 

    -- This is throwing error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'dbo.tr_TxnDetails'. 
    INSERT INTO tr_TxnDetails
    ([id], [etc])
    SELECT  a.[id],
            a.[etc]
    FROM #tr_TxnDetails as a
    WHERE not exists (select 1 from tr_TxnDetails as b where a.[id] = b.[id]);

How do I make sure the during the INSERT INTO statement to tr_TxnDetails it is not inserting a row with the same primary key: pid ?

this is possible duplicate http://stackoverflow.com/questions/2136273/primary-key-duplicate-record-bypass-to-next-insert — Anri, Dec 09 '12 at 20:17
Why are you trying to insert a row with a duplicate key? Does that even logically make sense? Don't just silence the error - remove the underlying problem. — usr, Dec 09 '12 at 20:20
@usr - xybrek is not trying to insert a row with a duplicate key, he's trying to _avoid_ doing that — Martin Wilson, Dec 09 '12 at 20:22
@MartinWilson but where is the duplicate value coming from? It is invalid in the first place. It shouldn't be there. Just turning on `IGNORE_DUK_KEY` is a fix for the symptom, not the cause. — usr, Dec 09 '12 at 20:26
@xybrek - is the primary constraint definitely just on id? I haven't got access to a db at the moment but what you're doing does look like it should avoid inserting duplicate ids — Martin Wilson, Dec 09 '12 at 20:28
Is this an occasional error you are investigating? The `WHERE NOT EXISTS` is not sufficient under conditions of concurrency. See [Only inserting a row if it's not already there](http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there) — Martin Smith, Dec 09 '12 at 22:08
It is possible that the first query produces duplicate rows, have you checked by running the select portion of the first insert on its own? Could you use select distinct.... — Steve Ford, Dec 09 '12 at 22:11

kgu87 · Accepted Answer · 2012-12-09T21:21:51.863

0

INSERT INTO #tr_TxnDetails       
SELECT 
    b.pid,
    b.etc
FROM tbl_SomeTableA as a
JOIN tbl_SomeTableB as b ON a.etc = b.etc 
    AND a.SomeColumn = b.SomeColumn 
  WHERE b.pid NOT IN (select distinct id from tr_TxnDetails) --<<--

INSERT INTO tr_TxnDetails
([id], [etc])
SELECT  a.[id],
        a.[etc]
FROM #tr_TxnDetails as a

edited Dec 09 '12 at 21:21

answered Dec 09 '12 at 21:02

kgu87

2,050
14
12

score 0 · Answer 2 · answered Dec 09 '12 at 23:41

I think that your first INSERT ... SELECT statement is producing duplicates and then these duplicates are causing primary key errors in your second select. Your WHERE EXISTS clause only guards against inserting a duplicate that is a duplicate of an existing row.

I will come to your query later, but just to show you can cause this error quite simply with the following set of statements:

create table TableA
(
  Pid INT PRIMARY KEY,
  etc INT
 );


INSERT INTO TableA
SELECT 1, 0
UNION 
SELECT 1, 2

and here is the error:

Violation of PRIMARY KEY constraint 'PK__TableA__C57059387F60ED59'. Cannot insert duplicate key in object 'dbo.TableA'.: INSERT INTO TableA SELECT 1, 0 UNION SELECT 1, 2

Now back to your query, the simple re-write is to ensure that the query only returns DISTINCT rows:

INSERT INTO #tr_TxnDetails       
SELECT DISTINCT
    b.pid,
    b.etc
FROM tbl_SomeTableA as a
JOIN tbl_SomeTableB as b ON a.etc = b.etc 
    AND a.SomeColumn = b.SomeColumn 

INSERT INTO tr_TxnDetails
([id], [etc])
SELECT  a.[id],
        a.[etc]
FROM #tr_TxnDetails as a
WHERE not exists (select 1 from tr_TxnDetails as b where a.[id] = b.[id]);

This should do the trick for you.

One further point is that in your example you should do away with the temporary table step unless there is a good reason for it, such as some other processing between those two statements. Here is the rewritten query:

INSERT INTO tr_TxnDetails       
SELECT DISTINCT
        b.pid,
        b.etc
FROM tbl_SomeTableA as a
JOIN tbl_SomeTableB as b ON a.etc = b.etc 
     AND a.SomeColumn = b.SomeColumn 
WHERE not exists (
    select 1 
    from tr_TxnDetails as c 
    where a.[id] = C.[id]
);

score 0 · Answer 3 · edited Dec 14 '15 at 10:01

DECLARE @ChoiceID INT 

SET @ChoiceID = (SELECT MAX([CHOICE_ID]) FROM BI_QUESTION_CHOICE) -- FOR SOMETABLE.ID

INSERT BI_QUESTION_CHOICE
                (
                    [choice_id],
                    [choice_descr],
                    [sequence],
                    [question_id],
                    [is_correct],
                    [created_by],
                    [created_dt],
                    [modified_by],
                    [modified_dt]
                )
                (SELECT @ChoiceID+ROW_NUMBER() OVER (ORDER BY @ChoiceID),  
                    pref.value('(ChoiceText/text())[1]', 'varchar(50)'),  
                    pref.value('(Sequence/text())[1]', 'varchar(50)') ,
                    @QuestionID, 
                    pref.value('(IsCorrect/text())[1]', 'bit'),
                    'mbathini',  
                     GETDATE(),  
                    'mbathini', 
                    GETDATE()  
                FROM @xmlstring.nodes('/ArrayOfBI_QA_ChoiceEntity/BI_QA_ChoiceEntity') AS Responses(pref))

How to deal with Violation of PRIMARY KEY constraint error

3 Answers3