Solved

Primary Key Constraint Violation

Posted on 1998-07-30
5
429 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

623 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