Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

Primary Key Constraint Violation

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
stevenvanooyen
Asked:
stevenvanooyen
1 Solution
 
VerolandCommented:
I Have found under heavy preasure that my SQL database gave me duplicate Identities. Just before it got marked suspect.
0
 
stevenvanooyenAuthor Commented:
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
 
cymbolicCommented:
Have you run DBCC against your table to see if it has problems?
Do this first, using CheckTable and CheckIdent
0
 
gmoriakCommented:
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
 
stevenvanooyenAuthor Commented:
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now