This takes one from a table that has a PK on date (or datetime, change accordingly), to a table with an auto_increment column. Then it takes a child table if you will (that relied on the previous date PK FK setup) in a new direction using the new parent table's auto_increment PK.

It could be done inline in the original tables. But I am risk adverse, and not wanting to mess up anyone's originals. At the end, do drops and renames. By the way, one interesting functionality with mysql rename is doing hot-swap renaming of tables that are safe even in high concurrency environments.

So in the below, think of origThought and nextThought as the before and after of a main table. And think of sotv1 and sotv2 as the before and after of some-other-table before and after conversion.

Create the Schema

create table origThought
(   myDate date not null, -- this can be a date or datetime, either one (but used throughout all tables consistently)
    thing varchar(10) not null,
    primary key(myDate)
);

create table nextThought
(   id int auto_increment primary key,
    myDate date not null,   -- remember that this was the driving force in old table as a PK
    thing varchar(10) not null
);

create table sotv1
(   -- some other table version 1
    id int auto_increment primary key, -- let's just say it started with this one
    ref_ToOldPK_myDate date not null,
    colC varchar(10) not null,
    constraint `asdf` foreign key (ref_ToOldPK_myDate) references origThought(myDate)
);

create table sotv2
(   -- some other table version 2
    id int auto_increment primary key,
    ref_ToOldPK_myDate date not null, -- comes along til we clean things up
    colC varchar(10) not null,
    newPkValue int not null, -- this is our new FK value pointing back to nextThought(id)
    -- when I say newPkValue, I mean the new PK value in nextThought table (not the PK value id in this table)
    constraint `asdf2` foreign key (newPkValue) references nextThought(id) -- our new PK in 'parent' table
    -- point with asdf is that asdf will fail cuz that name is already used, so named it something else if you need to
    -- many people don't care and leave out the `asdf` part
);
-- show create table sotv2; -- can see key and constraint names with this

Now load some data

insert origThought (myDate,thing) values('2015-01-02','frog'),('2015-01-07','fish');
-- convert our main table to nextThought
insert nextThought (myDate,thing) select myDate,thing from origThought order by myDate;
-- ok that is done, now onward
insert sotv1(ref_ToOldPK_myDate,colC) values ('2014-02-02',1); -- error 1452 showing FK was in force
insert sotv1(ref_ToOldPK_myDate,colC) values ('2015-01-02',77); -- success
insert sotv1(ref_ToOldPK_myDate,colC) values ('2015-01-07',88); -- success
insert sotv1(ref_ToOldPK_myDate,colC) values ('2015-01-02',99); -- success

Complete the conversion of sotv2 table

Now we merely want to patch our new sotv2 table using appropriate newPkValue.

Well not patch it because our FK is in place, so let's try to do it all in one insert statement.

Else it will fail due to FK (which is your friend). Your foreign key constraints are your friends.

insert sotv2(ref_ToOldPK_myDate, colC, newPkValue)
select t1.ref_ToOldPK_myDate, t1.colC, t2.id
from sotv1 t1
join nextThought t2
on t2.myDate=t1.ref_ToOldPK_myDate;

Look at results

select * from sotv2;
+----+--------------------+------+------------+
| id | ref_ToOldPK_myDate | colC | newPkValue |
+----+--------------------+------+------------+
|  1 | 2015-01-02         | 77   |          1 |
|  2 | 2015-01-07         | 88   |          2 |
|  3 | 2015-01-02         | 99   |          1 |
+----+--------------------+------+------------+

bingo, what we want to see.

Drop the prev date residue we don't need anymore in this 'child' table

alter table sotv2 drop column ref_ToOldPK_myDate;

Now we have

select * from sotv2;
+----+------+------------+
| id | colC | newPkValue |
+----+------+------------+
|  1 | 77   |          1 |
|  2 | 88   |          2 |
|  3 | 99   |          1 |
+----+------+------------+

That's even better !