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
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())
{
db.Database.ExecuteSqlCommand(sql);
}
Correct setting of entry with index 5, even if the current auto increment counter is 11.
source to share
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.
source to share
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.
source to share