recount to fix primary id in mysql

Question

after inserting 5 row of table with primary KEY and remove middle of data how to recount to fix primary counts? for example i have this row

|id |name

| 0 |mahdi
| 1 |mojtaba
| 2 |maryam

after remove mojtaba from row i have this:

|id |name

| 0 |mahdi
| 2 |maryam

how to recount id to fix this table:

|id |name

| 0 |mahdi
| 1 |maryam

why you want to do that? if u have this PK as FK is some other table then it would be problem... — Fahim Parkar, Dec 15 '12 at 07:44
You could try [OPTIMIZE TABLE](http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html) — bobthyasian, Dec 15 '12 at 07:46
@bobthyasian, `OPTIMIZE TABLE` does not change values in the rows. That would be very bad if it did! — Bill Karwin, Aug 15 '16 at 20:12

score 0 · Answer 1 · answered Dec 15 '12 at 08:01

0

I would say this will not be FIX for primary key, it would be MESS for a primary key.

If you have this primary key as foreign key for some other tables, then handling those while updating something like above would be very tedious work.

answered Dec 15 '12 at 08:01

Fahim Parkar

30,974
45
160
276

recount to fix primary id in mysql

1 Answers1