Incorrect Table Seed Value

• Mike Howell

  Mr or Mrs. 500

  Points: 521

  More actions

  April 23, 2007 at 2:00 pm

  #175935

  I am looking for ideas on what would cause a specific problem I am having.

  We are developing a database used for a web site.  When we were testing our work during unit test, we had an issue where one of our transaction tables failed when data was inserted.  The error was Violation of PRIMARY KEY constraint 'PK_TableIndex'. Cannot insert duplicate key in object 'dbo.TableName'.  The primary key of the table is an Identity integer field.  I checked the tables current seed value and it is 12941, although the last record in the table is 12947, causing duplicate ID values being entered into the table.

  The process of creating the test environment is to restore the database from production and apply our changes.  No changes to the procedures that insert this data are being done and there are no data conversion scripts occuring that may change this seed value.  On this release, this table is not being modified in any way.

  Are there any idea how the seed value can be changed?  At first, we wrote it off as a build glitch, but when we released the code to the test team, the same error occured.  I know I can reseed the table to get around the error, but am looking for potential causes to prevent this from occuring in the future.

  We are using SQL Server 2005 sp2. 

  Thanks for any help offered.

   

• Jayakumar Krishnan

  SSCarpal Tunnel

  Points: 4710

  More actions

  April 24, 2007 at 4:13 am

  #702099

  DBCC CHECKIDENT will help you well.

  Thanks
  Jay
  http://www.sqldbops.com