Rearrange primary keys in mysql

Question

How to rearrange primary key column values after deleting some rows from a table in MySQL?

Foe example; a table with 4 row of data with primary key values 1,2,3,4. When delete 2nd and 3rd rows, then the key value of 4th row change to 2.

Please help me to find solution.

score 6 · Accepted Answer · answered Oct 22 '13 at 07:17

Why do this? You don't need to rearrange your key since it's only number, identifier for record. It has no actual meaning - so let DBMS handle that. This is a very common mistake - trying to take DBMS role.

However, I'll answer your question for common case. In MySQL you can rearrange column with:

update t cross join (select @cur:=0) as init set t.col=@cur:=@cur+1

-this, however, can't be used with column under UNIQUE (so primary key as well) restriction since during update you'll possibly get duplicate records. You should drop restriction first before do that (and create it again after update).

sometimes you don't want to hit the maximum range of IDs, and you don't want to create a very big int just in case you had lot's of inserts and deletes, so rearranging IDs is not that bad. BTW I tried it on my database and it worked well on all my primary keys. Thanks a lot. — Mohammad, Jul 22 '15 at 10:10

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

One method is THIS ONE.

Other then that, you can simply drop the table which is primary and then again create it. This will do the job

edited May 23 '17 at 10:27

Community

1
1

answered Oct 22 '13 at 07:13

Vikas Arora

1,666
2
17
38

Thanks vkas, but in my table the primary key field is not auto increment. then how? – Naveen S Oct 22 '13 at 07:17

score 0 · Answer 3 · answered Oct 22 '13 at 07:16

Why do you want to change primary keys for your data? In general this is bad idea to do that, especially when integrity contstraints comes into the game. If you need to do such thing, I would say you have bad DB desing and you should take closer look on that aspect.

Rearrange primary keys in mysql

3 Answers3

Linked