July 13, 2009 at 2:30 pm
On trying to run a query to insert data into a table Panels whose PK is an Identity (aka "autonumber") field, I'm getting this error:
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.Panels' with unique index 'Panels$primarykey'.
The statement has been terminated.
Why would this happen? Isn't the whole idea behind the Identity data type that it will be a new, unique number?
When I look at the table design using SSMS, under the "keys" node, there's only one listed named Panels$ID. It's Identity spec is set to "yes" with seed=1 and increment=1.
Does the fact that the error message references unique index named "Panels$primarykey" indicate it looking a some other key definition?
This table already has a whole bunch of rows in it which were imported from elsewhere. I imported them with Identity Spec turned off, and then turned it on afterwards.
I can type a new row directly into the table, and the ID field gets incremented by 1 from the highest one already there, as I expect.
Even stranger, if I script the Panels table as a new, blank table, and run the same query on IT, the insert query runs fine, and the ID field gets inserted with 1,2,3,4... etc.
Here's the insert query:
INSERT INTO Panels( noun, tagset, serial, testdate, testtime, repdate, reptime, panelstat, WeekBeg, MfgMonth, Machine, TimeDate )
SELECT TST1.Noun,
max(
case
when [TST1].[Tagset] is null then 'none'
else [TST1].[tagset]
end
) AS Expr1, TST1.Serial,
max(TST1.date) AS testdate,
max(TST1.time) AS testtime,
Max(PAR1.date) AS repdate,
Max(PAR1.time) AS reptime,
Min(
case
when status='B' or repcode is not null then 'B'
else 'G'
end
) AS panelstat,
max([TST1].[date]-
datepart(dw,[TST1].[date])+1) AS WeekBeg,
max(dbo.fnmfgmonth([TST1].[date])) AS MfgMonth,
max(Right([TST1].[serial],1)) AS Machine,
Max(TST1.TimeDate) AS FirstOfTimeDate
FROM TST1 LEFT JOIN PAR1 ON (TST1.Serial = PAR1.serial)
AND (TST1.Noun = PAR1.noun)
AND (TST1.Image = PAR1.image)
GROUP BY TST1.Noun, TST1.Serial;
Why won't this run? Any clues appreciated!
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
July 13, 2009 at 2:43 pm
Hi there,
An Identity column will indeed generate a new number, but by no means is it guaranteed to be unique. It is simply one higher than the last generated, and the "last generated" counter can be reinitialized (or, to use the SQL term, reseeded).
If you take a look at BOL, you'll find ways to query where this counter is (hint : DBCC CHECKIDENT). If it's, say, to 5, then the next inserted record will have the value 6, the next 7, and so on. If, for any reason, values were inserted by using SET IDENTITY_INSERT ON/OFF to force a value in there OR if the counter was reseeded to a specific value below one present in the table, for example, if your table hold a record having the key 10 but your counter is at 9, your next insertion will fail because of the duplicate key violation.
Since when you create the table anew, the problem doesn't arise, but with the existing one it does, I suspect this is your problem.
To fix this, you can either reseed the property correctly or move your old data. At this point, it's up to you.
July 13, 2009 at 2:49 pm
Another reason might be an additional UNIQUE constraint other than the primary key.
As you've stated above, you checked the KEYS section in SSMS. Did you check the CONSTRAINT section as well?
If you can't find a reason you might want to post your table definition together (including any keys and constraints).
July 13, 2009 at 3:02 pm
In the infamous words of Rosanne Rosannadanna, "Never mind...."
The problem was simply that primary key Panels$ID and unique index "Panels$primarykey" are indeed two separate entities, and the 2nd one was what was being violated, while I was looking for violations in the first. Whoever did this naming (me....) should be taken out and shot. Or perhaps should go out and GET a shot of something..... by gosh, I think I'll do that!
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
July 13, 2009 at 3:14 pm
Glad we could help. 🙂
Edit: (Un)fortunately, we can't help you to get (a) shot. 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply