Primary key violation on insert

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

SQL Server 2000 Forums

Transact-SQL (2000)

Primary key violation on insert

Author

Topic

echovault
Starting Member

20 Posts

Posted - 2008-02-07 : 13:49:18

I have an insert that is giving me an error:
Violation of PRIMARY KEY constraint

The PK is an identity column and is not listed in the insert clause. It is an INSERT/SELECT statement, so is inserting multiple rows, but should just use the identity for the PK. Is there a condition that would cause the identity to be jumbled, like maybe it is trying to use a number that has been used already?

This happened spontaneously during a scheduled process that was running on 5 minute intervals. There were no ddl changes between the time of last succesful run and failure.

Thanks in advance!

cognos79
Posting Yak Master

241 Posts

Posted - 2008-02-07 : 14:13:11

are you sure you dont have other columns as part of identity key???

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-07 : 14:21:33

show us the code.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-07 : 14:34:52

also post the actual error that was returned. Perhaps the error was for a different table. Maybe because of a different statement in the transaction or perhaps a trigger on the table...

Be One with the Optimizer
TG

echovault
Starting Member

20 Posts

Posted - 2008-02-07 : 14:44:11

[code]
CREATE TABLE [dbo].[TagValues](
[ID] [int]IDENTITY (1, 1) NOT NULL
, [TagNameID] [int]NOT NULL
, [DatalogTime] [smalldatetime]NOT NULL
, [Value] [float]NULL
, [TimeInterval] [varchar](15)
COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
CONSTRAINT[DF__TagValues__TimeI__4F7CD00D] DEFAULT ('5 Minute')
, CONSTRAINT [PK__TagValues__4BAC3F29] PRIMARY KEY CLUSTERED([ID])WITH FILLFACTOR = 100 ON[PRIMARY]
, CONSTRAINT [UIX_TagValues_TagNameID_DatalogTime] UNIQUE NONCLUSTERED([TagNameID]
, [DatalogTime])ON[PRIMARY]
, CONSTRAINT [FK_TagValues_TagNameID] FOREIGN KEY([TagNameID])
REFERENCES [dbo].[TagNames]([ID])
)
ON[PRIMARY]
END

INSERT INTO dbo.TagValues (TagNameID, DatalogTime, Value)
SELECT N.ID, F.DateAndTime, F.Val
FROM dbo.FloatTable F INNER JOIN dbo.TagTable T ON F.TagIndex = T.TagIndex
INNER JOIN dbPlantPerf.dbo.TagNames N ON T.TagName = N.TagName
WHERE F.Status = 'W'

[/code]
And the error:
Violation of PRIMARY KEY constraint 'PK__TagValues__4BAC3F29'. Cannot insert duplicate key in object 'TagValues'.
The statement has been terminated.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-07 : 14:51:32

do you maybe have set identity_insert set to on?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

echovault
Starting Member

20 Posts

Posted - 2008-02-07 : 14:58:12

That crossed my mind, so I tried setting it off and running the insert. I get the same error. So for kicks I set it on to see what would happen. I get this error:

Explicit value must be specified for identity column in table 'TagValues' when IDENTITY_INSERT is set to ON.

Which convinces me that is not the issue, since I would have seen this error.
I set it back off, and still the orignal error.

cognos79
Posting Yak Master

241 Posts

Posted - 2008-02-07 : 15:08:42

echo...you have "UNIQUE NONCLUSTERED([TagNameID] , [DatalogTime])ON[PRIMARY]" in the table syntax...my guess is because of "unique" you are getting error

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-07 : 15:12:22

quote:
Originally posted by cognos79

echo...you have "UNIQUE NONCLUSTERED([TagNameID] , [DatalogTime])ON[PRIMARY]" in the table syntax...my guess is because of "unique" you are getting error

because of that he'd get an error on the primary key? that'd be really weird.

are you doing this insert concurrently by few users?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

echovault
Starting Member

20 Posts

Posted - 2008-02-07 : 15:16:13

Well I tried this and it seems to have fixed it.

DECLARE @idMax int
SELECT @idMax = MAX(V.ID) FROM dbo.TagValues V
dbcc checkident(TagValues, reseed, @idMax)

I tried the insert again and it works.
Still curious about the cause, but at least I have a fix, for now.

Thanks again everyone! I love this place!

echovault
Starting Member

20 Posts

Posted - 2008-02-07 : 15:19:38

And to answer the last question (sorry), the FloatTable gets data from multiple users on a regular basis, but the table I am doing the insert to is only inserted by the job that moves the data from one to the other. so there should not be any concurrency issues.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-07 : 15:20:44

looks like its internal counter got corrupt

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources