Change the start number of autogenerated field value

Sometimes you have auto-generated fields in tables. Most of the times this field is a ID-number of an item. And not every time you want to start numbers from 0.

For example, I have to export some historic data into a table with auto-generated number. I have 250 records to go in, so next number I want to see from the system is 251 or higher. But you can not change the starting point, and system is trying to count from zero. This leads to a unique field errors: “The record which is being saved contains records which must be unique within the database.

Auto-generated field start value is disabled

Auto-generated field start value is disabled

This can be avoided.

Go into database that serves your HR.Net and find table called OC_KEYGENERATOR. This table works as a counter for all the auto-generated fields in the system. So find the record with KEYID = “Table_field” and change the values of KEYVALUES and LASTUSEDVALUE to a desired counter start number. Make sure you put in relevant number: if you want next auto-generated record to be 251, then make KEYVALUES=251 and LASTUSEDVALUE=250. And so on.

Obviously, this hack is not documented in any of the Vizual documents.

This entry was posted in SQL Server and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *