Dropping the Foreign Key Constraint Using pt-online-schema-change

Foreign KeyIn this blog post, we’ll look at how to get rid of the unused Foreign Key (FK) constraint and/or related columns/keys with the help of pt-online-schema-change and the power of its plugins.

Before we proceed, here is a useful blog post written by Peter Zaitsev on Hijacking Innodb Foreign Keys.

If you are trying to get rid of an unused foreign key (FK) constraint and related columns from versions older than MySQL 5.6, or tables that cannot be executed with ALTER TABLE ... ALGORITHM=INPLACE because of limitations mentioned here (specifically, tables with 5.5 TIMESTAMP formats), you can use pt-online-schema-change.

For DROP FOREIGN KEY constraint_name  with pt-online-schema-change requires specifying _constraint_name rather than the real constraint_name. This is due to a limitation in MySQL: pt-online-schema-change adds a leading underscore to foreign key constraint names when creating the new table. Here’s is a simple example of one such case:

To drop the constraint, we are supposed to add an underscore prior to constraint_name FKID:

Below is one case where if, for some reason, you already have an FK constraint with an underscore the above method of adding an additional underscore to already underscored _FK will fail with an error while dropping it:

In such cases, we will have to make use of the --plugin  option used along with a file that calls the  pt_online_schema_change_plugin class and a hook after_alter_new_table to drop the FK constraint. For example, a table with the FK constraint with an underscore is:

Here we have a table with foreign key ___fkid using three underscores. Our plugin for dropping the constraint should be as follows:

NOTE: DROP FOREIGN KEY CONSTRAINT in the plugin has one underscore less than original foreign key constraint,  __fkId vs. ___fkId. Also, the alter statement will be NOOP alter (i.e.,  --alter ="ENGINE=INNODB").

Here is the pt-online-schema-change execution example with the plugin.

Share this post

Comment (1)

  • Siddhant Sawant Reply

    Also for knowing proper FK constraint name to be added in PT-OSC command ,
    Execute the PT-OSC command with PTDEBUG=1 and –dry-run option, and look for FK constraint name which the PT-OSC is trying to create table with and used that FK name in your final PT-OSC command .

    March 22, 2017 at 12:40 am

Leave a Reply