How to reorder a primary key?

Question

I have a table of 5700 records. The primary key is an integer. Now I noticed that some values are missing. Like this:

100 data
101 data 
102 data
104 data

103 is missing. How I can update all the rows so that the order becomes correct (104 becomes 103 in my example) in one SQL command?

Why do you need to do so? "Holes" in PK are normal situation - some rows are added, some deleted, there's nothing to worry about. — Tomasz Kowalczyk, Sep 11 '11 at 10:34
[Here][1] you can find an answer. [1]: http://stackoverflow.com/questions/1841104/mysql-auto-increment-filling-the-holes — mishau, Sep 11 '11 at 10:44

MLFR2kx · Answer 1 · 2016-02-14T09:04:09.190

23

Try this:

SET @var:=0;
UPDATE `table` SET `id`=(@var:=@var+1);
ALTER TABLE `table` AUTO_INCREMENT=1;

edited Feb 14 '16 at 09:04

answered May 13 '15 at 16:25

MLFR2kx

977
7
16

1

This works nicely. With innoDB cascades, the relationships are safe, and oh, use ALTER TABLE `table` auto_increment = 1; to fix the autoincrement reset. – frostymarvelous Sep 23 '15 at 13:57
Thanks for mention. The answer is edited to contain this query. – MLFR2kx Feb 14 '16 at 09:04
1

This actually works like charm. To me this is accepted answer for this question – Michael Feb 24 '17 at 18:12

score 10 · Answer 2 · answered Sep 11 '11 at 10:35

10

There is no point in doing this.

IDs from deleted records are not re-used on purpose - to make sure that references from other tables to previously deleted records don't suddenly point to the wrong record, for example. It is not a good idea to try to change this.

If you want a numbered list that has no holes, create a second int column that you re-order in your client program whenever necessary (i.e. when a record is deleted).

answered Sep 11 '11 at 10:35

Pekka

442,112
142
972
1,088

If you want a numbered list that has no holes, create a second int column that you re-order in your client program whenever necessary (i.e. when a record is deleted). GREAT! How to do it in PHP? – marcostT Sep 11 '11 at 10:54
4

If someone needs to do it he can have reasons for it, even if you think that there is no point. – sanmai Sep 11 '11 at 10:54
4

@sanmai it is not the job of the primary key to provide a numbered list. If you need that, maintain a second column. Misusing the PK for this is just dumb. – Pekka Sep 11 '11 at 10:57
It doesn't matter because your answer does not answer the question. – sanmai Sep 11 '11 at 10:58
5

@sanmai You are promoting blindly answering questions, even if that leads to bad practices. I think that is a terrible idea, and potentially harmful. Questioning the *question* should be the first thing one does before answering. See [What is the XY problem?](http://meta.stackexchange.com/q/66377) – Pekka Sep 11 '11 at 11:00
You can question question in the comments section under any question. – sanmai Sep 11 '11 at 11:03
@sanmai well, we'll have to agree to disagree here. – Pekka Sep 11 '11 at 11:06
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/3339/discussion-between-sanmai-and-pekka) – sanmai Sep 11 '11 at 11:07
Imagine you have a terms vocabulary which is unlikely going to change anytime soon. Now you're making a random word chooser. If you have no holes in your PK, the job becomes a piece of cake. – sanmai Jun 02 '16 at 01:01
Holes are an aesthetic pain only depending on usage. However there are good reasons to keep a key column sequenced. The performance optimization to sequence once vs the thousands of queries to an extra key extraction method for choosing a non empty row is a wise choice. – JSG Apr 06 '18 at 20:45

sanmai · Accepted Answer · 2016-06-02T00:58:34.967

7

Not sure about one command, but you can do it in four commands:

CREATE TABLE `temp` SELECT * FROM `orig_tbl`;
TRUNCATE `orig_tbl`;
-- counter doesn't reset in some old versions
ALTER TABLE `orig_tbl` AUTO_INCREMENT = 1; 
-- now we omit primary key column to let it seal the holes
INSERT INTO `orig_tbl` (`col1`, `col2`) SELECT `col1`, `col2` FROM `temp`;

Unless you're doing this to make it easier to select records randomly, you really should rethink your approach.

edited Jun 02 '16 at 00:58

answered Sep 11 '11 at 10:47

sanmai

29,083
12
64
76

this will mess you up if you use foreign keys – frostymarvelous Sep 23 '15 at 13:58
@frostymarvelous why in the first place would you want to reorder a primary key if you use foreign keys? – sanmai Jun 02 '16 at 00:55
I was going to reuse an old dataset in a new project. I just needed to clean it up. – frostymarvelous Jun 02 '16 at 08:45

score 1 · Answer 4 · edited Dec 19 '16 at 05:21

1

Another way, without truncating whole table:

-- Make Backup of original table's content
CREATE TABLE `orig_tbl_backup` SELECT * FROM `orig_tbl`;
-- Drop needed column. 
ALTER TABLE `orig_tbl` DROP `id`;
-- Re-create it
ALTER TABLE `orig_tbl` AUTO_INCREMENT = 1;
ALTER TABLE `orig_tbl` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

edited Dec 19 '16 at 05:21

sanmai

29,083
12
64
76

answered Feb 21 '16 at 08:02

SeveneduS

41
1
5

score 1 · Answer 5 · answered Sep 11 '11 at 10:39

I'd advise against messing with your PKs unless you really have no choice, it can cause breakage all over the place when that id column is referred by other tables. If the PK really tolerates no gaps, maybe the choice of PK was not ideal...

If you really think you should do this (and are sure nothing will break in other tables):

create a table with the same structure as the original one, but use type serial for id
copy data without id into that copy table - you'll now have a copy of original with thanks to serial a nice gapless id field
empty original table (or faster, create an identical copy and drop original)
copy data from copy table into original table including id

How to reorder a primary key?

5 Answers5

Linked