Primary Key Constraint error on on new unique numbers

Question

Post reply

Primary Key Constraint error on on new unique numbers

rebecca.moore.ctr

SSC Journeyman

Points: 93
More actions
April 24, 2012 at 7:24 am

#392096

I am trying to do a insert statement. The primary key isn't an identity field (not my choice). I get the lastID from the table, add 1 then do my insert. The first time I did this it worked, everytime since then it hits the primary key constraint. I looked at the constraint. it appears to be the same as all the other tables other than the constraint name of course. There are no other constraints on the table. No trigger happening either. I cannot change the table because it is part of a COTS product (Serena SBM)we are customizing.
No one else is using the system when I try my insert so I know my new id is the right number to insert.

Viewing 9 posts - 1 through 9 (of 9 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Please will you post your INSERT statement and the error message.

Thanks

John

rebecca.moore.ctr SSC Journeyman Points: 93 More actions · Answer 2

The actual database lives on a different network. My intent is to make a revision. So I am copying almost all the fields from 1 record to the new one. declare @newTSID int

Select @newTSID = Max(TS_ID)+1 from MYTABLE

Insert into MYTABLE

(TS_ID

, TS_UUID

,TS_ISSUETYPE

,TS_ISSUE_ID

...

)

Select

@newTSID

,NewID()

,0

,TS_Issue_ID +1000--place holder til later

...

From MYTABLE

Where TS_ID = @baseID -- passed into procedure

rebecca.moore.ctr SSC Journeyman Points: 93 More actions · Answer 3

error message is "Violation of PRIMARY KEY constraint 'TKMYTABLE'. Cannot insert duplicate key in object 'MYTABLE'.

I checked the primary key constrain on the table definition and the name matches the error. The definition only has the constraint on the first field of the table.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

April 24, 2012 at 7:52 am

#1478155

there there a trigger on the target table ?

MVDBA

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Can you temporarily comment out the INSERT part of the script to see what you are trying to insert? You could be attempting to insert more than one row with identical IDs. Given your WHERE clause, that shouldn't happen, but I can't think of any other explanation.

John

rebecca.moore.ctr SSC Journeyman Points: 93 More actions · Answer 6

There is a trigger but I disabled it to eliminate it as a possible problem.

rebecca.moore.ctr SSC Journeyman Points: 93 More actions · Answer 7

sorry stupid mistake my select was returning more than 1 record

CapnHector SSCoach Points: 16143 More actions · Answer 8

rebecca.moore.ctr (4/24/2012)
sorry stupid mistake my select was returning more than 1 record

not a problem some times it helps to post on the forums and something we say causes the lightbulb to turn on.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]