update primary key values based on a new sequence in MySQL / MariaDB

Question

Consider a table my_table with a column id_my_table as primary key with auto increment

CREATE TABLE `my_table` (
  `id_my_table` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `some_name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id_my_table`)
) ENGINE=InnoDB;

That primary key is referenced by some other tables.

I have some records and for the sake of this example let say I have five of them having id_my_table the following values: 1, 2, 3, 4 and 5. Because of a request by a client, which involves having a particular sequence from a legacy dataset, I updated the values of the primary key in order to match that sequence.

This is what I did:

ALTER TABLE `my_table` ADD COLUMN `my_old_id` INT UNSIGNED NULL AFTER `some_name`;

UPDATE `my_table` set `my_old_id` = `id_my_table`;

SET FOREIGN_KEY_CHECKS=0;

-- +100 so no duplicate error...
UPDATE `my_table` set `id_my_table` = `id_my_table` + 100;

UPDATE `my_table` set `id_my_table` = 25 where `id_my_table` = 1;
UPDATE `my_table` set `id_my_table` = 37 where `id_my_table` = 2;
UPDATE `my_table` set `id_my_table` = 58 where `id_my_table` = 3;
UPDATE `my_table` set `id_my_table` = 72 where `id_my_table` = 4;
UPDATE `my_table` set `id_my_table` = 96 where `id_my_table` = 5;

ALTER TABLE `my_table` AUTO_INCREMENT = 97;

UPDATE another_table at
INNER JOIN my_table mt on mt.my_old_id = at.reference_to_id_my_table
SET at.reference_to_id_my_table = mt.id_my_table;

SET FOREIGN_KEY_CHECKS=1;

Now everything is working as expected and new records have the correct sequence (based on the legacy one), but...

Can I continue normally? Or did I messed up some internal index and I'm not seing a potential issue down the road?

Answer 1

Short Answer: Looks fine.

Long Answer:

The "right" way to test this is to copy all the relevant tables to another server (or on the same server, but in a different database). Then perform operations that exercise things like fetching via the Foreign Key or adding a new row (which will get id=97).

One more thing to do: Put START TRANSACTION and COMMIT around the set of UPDATEs. This single transaction will assure that all the Updates or non are done.

Offhand I see nothing wrong with your code. (I assume that Update with the Join will be done for each other table that references my_table.) (If you have already run all the Update, don't worry about the transaction. Think of it as a tip for 'next time'.)

As for how AUTO_INCREMENT works, I don't see any future gotchas. The new ids will be 97 and up; the numbers in the gap will be forever missing.

Be aware that REPLACE is DELETE + INSERT, so it will, in all(?) cases, lose the auto_inc id.

Did you have to write the 5 Updates? It might have been easier to build a table with the mapping, then apply it to all relevant tables. In the case of the main table, only if there is a risk of dup ids, would you need some trick (like +100) may be necessary.

Consider doing DROP COLUMN later.