- Locate all tables with IDs in them (
FOREIGN KEYs, etc). Figure out the exact numbers for below (I took your 10M and 100K as exact.)
- Take your application "down".
UPDATE tbl SET x_id = x_id - 9900000 WHERE x_id >= 10000000; for each such table.
ALTER TABLE ... AUTO_INCREMENT = ... (or otherwise make sure it gets lowered)
- Bring your app back up.
It would be wise to experiment before launching into the entire task. If you have a test platform do it all there. If not...
Limit the damage (for testing):
WHERE x_id >= 10000000 AND x_id < 10000010 LIMIT 11 -- then check that the number of rows changed was no more than 10. And verify that all actions on those users are still OK. You still must go through all 6 steps, but if something breaks, you will have less damage to clean up. And, in doing this small test, you will (hopefully) figure out all the
UPDATEs that are needed.
To be more daring,... Don't disable/enable the FKs. Instead, figure out what order to do the
UPDATEs in so that the FKs won't complain. Verfiy that the
CASCADEs will do what you need. This also gives you more confidence that the values will be correctly fixed; that is, no dangling messed up FKs.
(A caveat on
AUTO_INCREMENT - The handling of such has changed with 8.0; my steps may be changed a bit.)
As a Postlog, ask yourself whether FKs have been more a benefit or more a pain.