Hi
I am reading the doc for the relevant tool in order to use it, well, actually I have performed a test with great success but when i came across to tables with foreign keys i got troubled . So i have a few questions on the subject which i don’t understand:
I dont understand this. I mean, why does the child table “follows” the old table? The way I think of it , the child table will look for the foreign keys according to the table name (right?), and since the new table (the altered) was renamed and has the proper name, how come the child table tries to reference the foreign keys to the old one. Also, as it is stated on the manual, the old table is dropped which means the child table tries to reference a table that doesn’t exist?
What change on the foreign keys will be made on the child table? I mean, the table name and the rows are the same …
I am not an expert of mysql so excuse me in advance if i ask something obvious.
Basically pt-osc does the following steps:
Step 1: Create the new table.
Step 2: Alter the new, empty table. This should be very quick,
Step 3: Create the triggers to capture changes on the original table and
Step 4: Copy rows.
Step 5: Rename tables: orig → old, new → orig
Step 6: Update foreign key constraints if there are child tables.
Step 7: Drop the old table.
So what #7 does is it drops the foreign keys referencing the old table and creates new ones for the new table.
[url]https://dev.mysql.com/doc/refman/5.0/en/rename-table.html[/url]
Note that if the table being renamed is a target for any InnoDB foreign keys, the keys will continue to point to the swapped-out table, not its replacement, and referential integrity for newly added rows may be different than what you might be expecting… A fully formed table swap needs to drop all referencing foreign keys from all other tables and add new ones to the swapped-in table, which means index/table rebuilds over the entire database schema. In practice, this means the atomicity only works for databases where foreign keys aren’t used.