MySQL fixing autoincrement gaps in two tables

Question

I have two tables like so;

id_image   foo    bar
1          3      5
2          8      1
3          17     88
7          14     23
8          12     9


id_image   bar    foo
1          2      3
1          5      6
2          18     11
2          10     12
3          8      21
3          17     81
7          29     50
7          1      14
8          10     26
8          27     34

There is a gap in the autoincremented id_image in the first table. In the second table, the id_image refers to the id_image in the first table, and there's two of each ID in there.

Notice: This table is theoretical. I have no idea where the gap is exactly, or whether or not there are even multiple gaps. All I know is that the first value is 1 and the last value is higher than the total row count.

Now, I'd like to fix this gap.

Before you say that the gaps don't matter and if they do, it's bad database design, let me tell you; I agree with you.

However, what I'm dealing with is a (hopelessly rear end backwards) third-party open source system to which I need to import a huge amount of existing data that doesn't have cross-referenceable IDs into multiple tables. The only way I can make sure that the same data gets a matching ID in every table throughout the system is to input it sequentially, and that means I can't have gaps.

So what I do now need to do is;

1. Fix the gap in the id_image column in the first table, so that the last value matches with the row count.
2. Edit the id_image column in the second table so that its value corresponds to the same row is corresponded to before the gap fix.

How would I begin to do this? I understand that this might be outside the capabilities of the MySQL query language, so PHP answers are also acceptable. Thanks! :)

Answer 1

ALTER TABLE table2
ADD FOREIGN KEY FK_IMAGE (id_image)
REFERENCES table1 (id_image)
ON DELETE CASCADE
ON UPDATE CASCADE;

SET @currentRow = 0;

UPDATE table1 INNER JOIN (
    SELECT @currentRow := @currentRow + 1 AS id_image_new, id_image AS id_image_old
    FROM table1
    ORDER BY id_image ASC) t on t.id_image_old = table1.id_image
SET table1.id_image = t.id_image_new;

ALTER TABLE table1 AUTO_INCREMENT = 1;

The FK will automatically update ids of your 2nd table accordingly.

I'm not sure at all but in some older versions of mysql, update a table that you are referencing within a subquery of the update could crash. If so, just create a 2nd table and fill it up (inserts), then delete the old one and rename the new one.