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.

1 Comment
Newest Most Voted
Inline Feedbacks
View all comments