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 #.
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.
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.