2

Just a few hours ago, we started getting an error that is "impossible" as far as I can tell. Error is

Violation of PRIMARY KEY constraint 'PK_RntlApp'. Cannot insert duplicate key in object 'Rental_Application'.

The reason I think this is impossible is that the primary key of the Rental_Application table is called "File_ID" and it is an autonumber integer. The code that is causing this has been working for literally 12 years straight.

This has to be related -- we used to replicate this database. The 2 databases both would reserve a range of identity values that they could insert so that data could be inserted in both databases, and merged together without incident.

We broke this replication a couple of months ago. All has been working fine until just recently.

Any help or pointers would be greatly appreciated.

  • You can override an autonumber when doing an insert, so it's entirely possible that someone manually inserted a tuple, thinking they were using a number so large that your DB would never hit it. – Chris S Sep 03 '10 at 23:23
  • Hi Chris -- good thought, and normally it would be possible, but in this case I'm the only guy who's ever done any coding on this system, and the only thing that inserts into this table is 1 piece of code that I wrote, so I know (with a really really high degree of certainty) that no one is using Identity Insert. Thanks for the thought, though. –  Sep 07 '10 at 16:44

1 Answers1

4

You can try running the following to see what value is the current identity value. Then see if that value is already in the database.

SELECT IDENT_CURRENT('table_name')

You may be out of your planned range of values and hit an existing value. Or, maybe some process or person inserted a value in the identity column and avoided the auto-number process.

You can use the following command to reset the identity seed value.

DBCC CHECKIDENT (<table name>, RESEED, <new value>)

So, you can move the next value to an unused range or even the maximum current value in the column.

bobs
  • 206
  • 2
  • 5
  • I am actually the person who asked the question (OpenID doesn't work from my work). YOU ARE AWESOME! The next identity number is 410060, but the max of that column in the db is actually 410316. So...I think that what happened was that it finally reached the end of its reserved range, but didn't think it was in replication anymore, and tried to start using values in a range that the other replicated DB had actually used before we broke the connection. Ugh -- thank you so much, bobs. Anyone know how to set the next number to use with the least amount of DB changes? – Matt Dawdy Sep 06 '10 at 03:18
  • @Matt, I added information to set the identity value. – bobs Sep 06 '10 at 16:48
  • Thank you bobs. I just realized that since the DB has close to 100 tables, and all of them were replicated in much the same fashion, most if not all of them might start encountering the same error. I'm going to start creating some statements to check every table and then run the script you suggested on them. Only 100 tables means it'll take some time, but it is possible. Thanks for all your help. –  Sep 07 '10 at 17:52