Solved

Primary Key Constraint Violation

Posted on 1998-07-30
5
428 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

752 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