Primary key violation error when I'm not violating the primary key

  • I need to insert some rows from a temporary table into a persistent table. The persistent table has a primary key composed of three columns, so I have to make sure I don't insert any combination of the three columns that already exists in the persistent table.

    I've built protection into the SELECT statement to prevent inserting duplicates by doing an outer join from the source table to the destination table. Pretty standard stuff...except it's not working.

    INSERT INTO dbo.tblSpot_Used_Counts

    (

    SR_ID

    ,IR_ID

    ,Ordernr

    ,Used_Count

    ,Used_Count_Autofill

    )

    SELECT

    u.SR_ID

    ,u.IR_ID

    ,u.Ordernr

    ,u.LocalTimesUsed

    ,u.LocalTimesUsedAF

    FROM #Usage u

    LEFT OUTER JOIN tblSpot_Used_Counts tsc ON

    tsc.SR_ID = u.SR_ID

    AND tsc.IR_ID = u.IR_ID

    AND tsc.Ordernr = u.Ordernr

    WHERE tsc.Used_Count IS NULL

    I've verified in the data that in fact no duplicates are being created.

    I've dropped the primary key on the target table and replaced it with a unique constraint, but that gives me an error that says the unique constraint is being violated.

    If I then drop that constraint and call the stored proc, the insert doesn't produce an error. If I then query the data, it shows that there are no repeated combinations of SR_ID, IR_ID and Ordernr.

    So we're getting primary key/unique constraint errors when we're not actually violating the primary key/unique constraint.

    I've got to get past this in a hurry, but I'm completely stumped. Anybody got any ideas?

  • A quick thougt, any difference in the select if you add the DISTINCT directive?

    😎

  • If you're getting that error, you're either trying to insert a row that already exists or there are duplicates in the set you're inserting. There's no other reasons you'd be getting a pk violation.

    Try using a NOT EXISTS instead of the join. Should be equivalent, but a not exists is harder to mes up (eg if the column you're checking for null is nullable itself)

    Check this to see if there are dups in the source data:

    SELECT

    u.SR_ID

    ,u.IR_ID

    ,u.Ordernr

    ,count(*)

    FROM #Usage u

    group by u.SR_ID

    ,u.IR_ID

    ,u.Ordernr

    order by count(*) desc

    What are the data types involved?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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