In my development I use InnoDB tables almost exclusively because I can not bring myself to build a database without referential integrity. SQLyog does a great job of structure sync and even data sync if there are no foreign key violations, but of course fails when there are violations.
There are 2 ways to solve this: the quick-and-dirty would be to allow the user (me!) to re-order the tables in the sync tool so that I can have the “base” (usually lookup) tables transferred before the tables that refer to them. The nicer solution would be for SQLyog to determine those relationships and order the tables automatically.
This will of course not solve the problem where the source database has deleted a value in a lookup table, and you then try to delete the corresponding row in the target database if there is data in the target database using that key…to handle this makes a “completely correct” solution a little more difficult – right now I would settle for automatic re-ordering!
