Solved

Primary Key Constraint Violation

Posted on 1998-07-30
5
419 Views
Last Modified: 2010-03-19
In attempting a series of inserts into multiple tables, I am consistently experiencing a Primary key constraint violation - "Msg 2627, Level 14, State 1
Violation of PRIMARY KEY constraint 'aaaaatblInstitutions_PK': Attempt to insert duplicate key in object 'tblInstitutions'.
Command has been aborted."  This database has been upsized from MS Access 97.  The first time I attempt to insert into this table and the others that accompany this insert, it works fine.  After the initial time, it begins to error. Note that we are not in any way writing explicitly to the primary key which has an identity key and a seed and increment of 1.  The application that is writing to the db is Cold Fusion.  There is a transaction around the set of inserts and select statements.  It is interesting that the error also occurs when attempting an insert within SQL Enterprise Manager Query tools.
0
Comment
Question by:stevenvanooyen
5 Comments
 
LVL 2

Expert Comment

by:Veroland
ID: 1089329
I Have found under heavy preasure that my SQL database gave me duplicate Identities. Just before it got marked suspect.
0
 

Author Comment

by:stevenvanooyen
ID: 1089330
There has been very little pressure on any of the db's that this is happening on (all of the db's come from the same template - there is no specific interval as to when the error will begin to occur).
0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1089331
Have you run DBCC against your table to see if it has problems?
Do this first, using CheckTable and CheckIdent
0
 
LVL 3

Accepted Solution

by:
gmoriak earned 200 total points
ID: 1089332
Drop the identity column for the table and manually insert a new row with the correct next value for the identity column while the identity property is off.  Then turn the identity property back on.  The internal counter used can get corrupted when the data is loaded from other sources or even in a load database operation.  This will reset the counter to the correct value.
0
 

Author Comment

by:stevenvanooyen
ID: 1089333
The information was helpful.  I have created a script that runs dbcc checkident on all of my tables.  This works most of the time, but occasionally the problem persists past that.  gmoriak talked about dropping the identity column and rebuilding it with the identity property off.  I hope that there is a way that is less time consuming and a way to prevent this error.  
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question