How to update autoincremented id when delete row in table?

Question

I am creating application that uses MYSQL database in C#. I want to delete row and update autoincremented value of id in table. For example, I have table with two columns: id and station, and table is station list. Something like this

id station
1 pt1
2 pt2
3 pt3

If i delete second row, after deleting the table looks something like this:

id station
1 pt1
3 pt3

Is there any way that I update id of table, for this example that id in third row instead value 3 have value 2? Thanks in advance!

Answer 1

An autoincrement column, by definition, should not be changed manually.
What happen if some other tables use this ID (3) as foreign key to refer to that record in this table? That table should be changed accordingly.
(Think about it, in your example is simple, but what happen if you delete ID = 2 in a table where the max(ID) is 100000? How many updates in the main table and in the referring tables?)

And in the end there is no real problem if you have gaps in your numbering.