Drop Tables Referenced by Other Tables - Migration to MySQL

Often during data migration you need to recreate a parent table and reload its data from Oracle to MySQL. The problem that this table already exists in MySQL and has child tables that have foreign keys to the parent table.

Let's assume MySQL has the following tables and data after the initial migration:

MySQL:

  -- Parent table
  CREATE TABLE states
  (
      abbr CHAR(2) NOT NULL PRIMARY KEY,
      name VARCHAR(70)
  );
 
  INSERT INTO states VALUES ('FL', 'Florida');
 
  -- Child table
  CREATE TABLE cities
  (
      name VARCHAR(70),
      state CHAR(2),
      FOREIGN KEY (state) REFERENCES states(abbr)
  );
 
  INSERT INTO cities VALUES ('Jacksonville', 'FL');

Now let's reload states table from the source database.

The states table is referenced by cities table, so MySQL does not allow you to remove the parent table:

  DROP TABLE states;
  # ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

But you disable foreign key check, MySQL will allow you to remove the table:

  -- Disable foreign key check
  SET FOREIGN_KEY_CHECKS = 0;
 
  DROP TABLE states;
  # Query OK, 0 rows affected (0.07 sec)
 
  -- Enable foreign key check
  SET FOREIGN_KEY_CHECKS = 1;

Note that although the parent table states was removed, the child table cities still has the foreign key constraint to states table.

Now when you recreate the parent table you must specify the primary key clause in the CREATE TABLE statement, other the table will not be created:

  -- Try to recreate the parent table without primary key
  CREATE TABLE states
  (
      abbr CHAR(2) NOT NULL,
      name VARCHAR(70)
  );
  # ERROR 1005 (HY000): Can't create table 'states' (errno: 150)
 
  CREATE TABLE states
  (
      abbr CHAR(2) NOT NULL PRIMARY KEY,
      name VARCHAR(70)
  );
  # Query OK, 0 rows affected (0.11 sec)

Let's load the table with different data:

  -- Now states table has different data
  INSERT INTO states VALUES ('CA', 'California');

You can see that this approach allows you to drop the parent table, leave foreign key constraints for the child tables, but it does not validate the new data loaded to the parent table, so you can have invalid keys.

If you are sure that the parent table has valid data in the source database, this approach to recreate and reload a parent table is the fastest as you do not need validate all foreign key constraints.

Also this approach is the easiest especially if you have multiple parent tables and many foreign key constraints.

Another approach to recreate and reload a parent table is to remove foreign key constraints on child tables, load the new data to the parent table, and then recreate foreign key constraints.

First, you have to find all foreign key constraints referenced to the parent table:

  -- Find all foreign key constraints to states table
  SELECT constraint_name, table_name 
  FROM information_schema.referential_constraints
  WHERE referenced_table_name = 'states';

The query returns:

Now you can remove the foreign key constraint by its name:

  -- Remove foreign key for child table
  ALTER TABLE cities DROP FOREIGN KEY cities_ibfk_1;

Then you can drop the parent table, create it, insert the new data and recreate the foreign key constraints:

 -- Recreate foreign key constraints after data load
 ALTER TABLE cities ADD FOREIGN KEY (state) REFERENCES states(abbr);

This approach does not allow you to create foreign key constraints on inconsistent data, but you have to drop and re-create each foreign key constraint. To re-create a foreign key constraint you have to know on which columns it was defined.

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - March 2013.