SQL bug? - Violation of Primary Key

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL bug? - Violation of Primary Key

Author

Topic

Hal9000
Starting Member

3 Posts

Posted - 2011-03-14 : 15:54:27

Check this out:

insert into tblTransReg (audit_user, audit_timestamp,lastrev)
VALUES (@username,GETDATE(),0)
SELECT @NewInternalReferenceNumber = @@IDENTITY

declare @count_of_PK_tblTransHdr int
select @count_of_PK_tblTransHdr = COUNT(*) from tblTransHdr where InternalReference = @NewInternalReferenceNumber
print @count_of_PK_tblTransHdr

The count prints 0, but the very next statement, an insert, sporadically -- once or twice in a batch of 137 or sometimes not at all -- throws this error:
Violation of PRIMARY KEY constraint 'PK_tblTransHdr'. Cannot insert duplicate key in object 'dbo.tblTransHdr.

Here is the SQL for the Insert:

insert into tblTransHdr (InternalReference,Revision,TransactionDate,CreateDate)
SELECT @NewInternalReferenceNumber, 0, h.TransactionDate, @CreateDate
FROM tblWrkTransHdr as h
WHERE WorkReference = @WorkReferenceNumber

The primary key for dbo.tblTransHdr is InternalReference, Revision.

Question 1: if @count_of_PK_tblTransHdr is 0, how can we have a sporadic primary key violation on the Insert?

Question 2: Should we try rebuilding the Primary Key for dbo.tblTransHdr?

I’ve tried this with and without using a Transaction, and get the violation both ways.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-14 : 16:20:00

I am not following your post. Your COUNT(*) is on tblTransReg, and your error is on tblTransHdr. You apparently already have the row inside tblTransHdr.

Rebuilding the primary key is very unlikely to fix your problem as this is a data problem. A transaction doesn't help get or avoid the error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-14 : 16:31:52

I'm not sure if this is the issue, but instead of using @@IDENTITY try using the @SCOPE_IDENTITY() function. Also, if using SQL 2008 you could use an OUTPUT clause to get the identity.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-14 : 16:59:18

quote:
if using SQL 2008 you could use an OUTPUT clause to get the identity

This also works in SQL 2005.

Hal9000
Starting Member

3 Posts

Posted - 2011-03-14 : 20:24:28

That's the problem, the row is not already in tblTransHdr, although the error says that. The count is on tblTransHdr, not tblTransReg, although I am using @@IDENTITY from tblTransReg to serve as part of the primary key in tblTransHdr. This count always comes up zero -- the key does not exist in tblTransHdr.

This problem is driving me crazy. I'll try the suggestions, but I don't see how they can help because the @@IDENTITY value returned is correct. What is incorrect is SQL Server sometimes (very infrequently) thinking the value already exists in the child table.

Hal9000
Starting Member

3 Posts

Posted - 2011-03-14 : 20:51:41

I found the problem. My insert statement is sometimes returning two records, when it should always return only one. That is my bug. As for why it's sporadic, that's what I'll find out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources