Mysql – the optimal solution for converting a database schema

foreign keyinnodbmyisamMySQLschema

I actually want to get rid of the existing constraints in the database which I know sounds crazy but we are upgrading our application to a new framework which relies on the foreign key constraint names plus we're doing a bunch of cleanup and renaming of fields, tables etc.

I suppose a better way to frame the question is that given an existing database that needs to go through the following:

  • Create all brand new foreign key constraints with names that work within the framework
  • Tables will be renamed and/or massively restructured including renaming fields that are currently part of existing foreign key relationships

What is the best way to accomplish this?

My solutions thus far have been:

  1. Create a conversion script (in php) that will run DROP FOREIGN KEY for all of the constraints in the database. This has resulted in weird errors occurring during the later steps of the process when trying to ALTER TABLES and add in the new constraints. Also, the constraints were removed but I get errors if I try to DROP the keys themselves.
  2. Instead of using PHP and PDO, write the conversion script using straight SQL and use a CURSOR to DROP the constraints.
  3. Instead of dropping the constraints themselves, convert the tables to MyIsam and DROP the keys then convert back to Innodb before continuing with the rest of the conversion process.
  4. Create a brand new schema from scratch and then move the data from the old schema to the new.

I would like to know which of these approaches would be considered the most valid? Or if there is a better way of doing this?

UPDATE (this information was requested in the chat room so I thought I would add it here):

  • The current schema has 68 tables, the new schema will be closer to 55.
  • The largest table has just over 800,000 rows and in general our tables are in the tens of thousands.
  • The entire database is roughly 240 MB but most of that is in a single table that stores a large text field. We'll be compressing this during the conversion.

Best Answer

There comes a point when a step-by-step clean-up becomes more work than a clean slate and migrate approach. System availability and time to migrate may factor in to the decision when dealing with larger volumes but at this size, not an issue.

Key factors for me here are:

  • Renaming foreign key constraints to fit a new application framework.
  • Refactoring a significant proportion of existing tables.
  • Low volume of data.

In this situation I'd be very tempted to design a new schema that fits the model you now require and create the necessary scripts to migrate data across (your option 4).

Related Question