How to preserve ID generated from a PRIMARY KEY when moving data into a new table?
When you have a Primary Key with an auto_increment it will generate a new ID only if you insert a NULL value. If you set ID=4 in your INSERT, the ID will be 4 so you'll not loose your ID during your "move" operation.

We don't have the "SEQUENCE" notion like in Oracle database so your "global ID" problem it's not so easy to do.

Maybe you can try something like this (but it'll add complications for just a 4 millions rows table)

Create a table used for generates your "Global ID", with one int filed auto_incremented:

CREATE TABLE test.sequence_table (next_id int primary key auto_increment);


When you want insert a new row in your child table:

Solution 1: With SELECT in information_schema

BEGIN; -- Start a new Transaction to ensure consistency

INSERT INTO test.sequence_table values (NULL); -- Generate a new ID

SELECT @next_ID:=(auto_increment - 1) FROM information_schema.tables WHERE table_schema="test" AND table_name="sequence_table"; -- Here I use a MySQL Variable but you can store it in PHP or whatever

INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable

COMMIT; -- Wonderfull :)


Edit after ypercube comment:

Solution 2: With LAST_INSERT_ID()

BEGIN; -- Start a new Transaction to ensure consistency

INSERT INTO test.sequence_table values (NULL); -- Generate a new ID

SELECT @next_ID:=LAST_INSERT_ID(); -- Use of the MySQL function LAST_INSERT_ID()

INSERT INTO child_table values (null, @next_ID, "Max", "SQL"); -- Use your variable

COMMIT; -- Wonderfull :)