Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Primary Key Constraint Violation

Posted on 1998-07-30
5
Medium Priority
?
431 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 600 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

704 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