CODEWITHSUNDEEP
mysql
Rohan Goswami
Rohan Goswami

Reputation: 13

Auto update of MySql row numbering

I have made a table in MySql with a column "id" as auto_increment and Primary_key. I initially made 3 rows, namely 1,2,3. I deleted no. 2 row but the numbering remains 1, 3. How do I change it to 1,2.
MySql Table

Upvotes: 1

Views: 42

Answers (1)

Jacobm001
Jacobm001

Reputation: 4539

The short answer is: you don't. Changing this is a multi step problem that can be very error prone if you don't do it right. Frankly, it rarely (if ever) makes sense to do so.

Things involved in such a task:

  1. Delete a row you don't want (you've done that)

  2. Subtract 1 from the id of every id that's >= to the row you just deleted.

  3. Update the auto increment counter to be at 1 less than the current.

  4. Do the same thing for all tables in which this is a foreign key.

If you want to delete multiple rows, the logic gets much more complicated very quickly. Either way, it will take considerable resources and will wreak havoc with your foreign keys.

Such an action really goes against the principles of how one would normally use a database. An RDMS is not a flat file like an excel spreadsheet. Just delete the rows and let there be a gap in the auto incremented ID, it really doesn't make any difference whatsoever to your application.

Upvotes: 2

Related Questions