Reorder / reset auto increment primary key

Question

I have a MySQL table with an auto increment primary key. I deleted some rows in the middle of the table. Now I have, for example, something like this in the ID column: 12, 13, 14, 19, 20. I deleted the 15, 16, 17 and 18 rows.

I want to reassign / reset / reorder the primary key so that I have continuity, i.e. make the 19 a 15, the 20 a 16, and so on.

How can I do it?

Answer 1

You can remove the primary key auto increment functionality of that column, then every time you update that column run a query before hand that will count all the rows in the table, then run a loop that iterates through that row count inserting each value into the respective row, and finally run a query inserting a new row with the value of that column being the total row count plus one. This will work flawlessly and is the most absolute solution to someone trying to accomplish what you are. Here is an example of code you may use for the function:

$table_row_count = mysql_result(mysql_query("SELECT COUNT(`field_1`) FROM `table`"), 0);
$viewsrowsdata = mysql_query("
    SELECT `rank`, `field1`, `field2`, `field3`, `field4`
        FROM (SELECT (@rank:=@rank+1) as `rank`, `field1`, `field2`, `field3`, `field4`
            FROM (SELECT * FROM `views`) a
            CROSS JOIN (SELECT @rank:=0) b
            ORDER BY rank ASC) c
");
while ($row = mysql_fetch_assoc($viewsrowsdata)) {
    $data[] = $row;
}
foreach ($data as $row) {
    $new_field_1 = (int)$row['rank'];
    $old_field_1 = (int)$row['field1'];
    mysql_query("UPDATE `table` SET `field_1` = $new_field_1 WHERE `field_1` = $old_field_1");
}
mysql_query("INSERT INTO `table` (`field1`, `field2`, `field3`, `field4`) VALUES ('$table_row_count' + 1, '$field_2_value', 'field_3_value', 'field_4_value')");

Here I created an associative array which I had appended on a rank column with the query within a select query, which gave each row a rank value starting with 1. I then iterated through the associative array.

Another option would have been to get the row count, run a basic select query, get the associative array and iterate it through the same way but with an added variable that updates through each iteration. This is less flexible but will accomplish the same thing.

$table_row_count = mysql_result(mysql_query("SELECT COUNT(`field_1`) FROM `table`"), 0);
$viewsrowsdata = mysql_query("SELECT * FROM `table`");
$updated_key = 0;
while ($row = mysql_fetch_assoc($viewsrowsdata)) {
    $data[] = $row;
}
foreach ($data as $row) {
    $updated_key = $updated_key + 1;
    mysql_query("UPDATE `table` SET `field_1` = '$updated_key' WHERE `field_1` = '$row['field_1']'");
}
mysql_query("INSERT INTO `table` (`field1`, `field2`, `field3`, `field4`) VALUES ('$table_row_count' + 1, '$field_2_value', 'field_3_value', 'field_4_value')");