How can I re-assign the auto-incrementing primary key value?

Is it possible to set the value of an autoincrement field? If a record is deleted with a primary key of 5, but the current auto-increment count is 10, is there a way to re-insert that record while keeping its primary key at 5 (instead of 11)? I am trying to do this with Entity Framework 4.1 in C #.


source to share

4 answers

Even though there were some good suggestions here, I started to understand a few things while working with the solution to this question. Disclosing that the database was MySQL might have helped here, but at the time I didn't realize the difference would be that significant. In fact, there is no auto-incrementing primary key insert lock in MySql. As a result, this code works to insert a record with a primary key less than the current auto-increment index value:

        var sql = @"INSERT INTO Work (
            WorkId ,
            LotId ,
            Description ,
            VALUES (
            '5',  '5', 'This Works'
        using (var db = new Context())


Correct setting of entry with index 5, even if the current auto increment counter is 11.



I think you can also use

Set Identity_Insert TableName ON 
 Update your Id value
Set Identity_Insert TableName OFF




Quote from Tom Haigh :

You can drop the primary key column and recreate it. All ids will be reassigned, I assume in the order in which the rows were inserted.

However, I'm not sure why you want to do this, especially if you have other tables that contain a foreign key to that table. If so, you will need to update these fields as well, in which case recreating the column would not be a good solution. You could rename the column instead, remove the autoinc / primary key property, then create a new autoinc primary key column. Then you will have old and new ids that you could use to update such foreign keys.



You can turn off the identity specification . Your primary key will remain untouched, but you can insert a new record with an ID of your choice. After you have inserted the record, you would like to re-enable the identity specification.



All Articles