[MySQL] 外部キーを動的に削除

概要

本記事を通して実行すると動的に外部キーを削除できるようになる！

railsのridgepoleを使うとデータベースを再作成するときに外部キー制約エラーが発生して、都度外部キーを削除しないといけない場面が発生。開発をしている最中のため、外部キーが増えた時のメンテナンスも大変になってしまうため動的に削除できないかを検討。
(05/20追記) ridgepoleの使い方が間違えていたみたいです。。

だけど...ネットで情報が全くない！！！
静的に削除する記事は見かけたが、意外とMySQLで動的外部キー削除の記事が見当たらなかったので、Qiitaに備忘録として残すことにした。

そもそもストアドプロシージャの情報が少なすぎる...。
あまり活用されていないのかな？？

実行環境の情報

• MySQL 5.7
• ストアドプロシージャを利用
• rootユーザーで実行

ストアドプロシージャー実装

1. 外部キー情報を保持しているシステムテーブルinformation_schema.key_column_usageの制約(constraint_name)カラムを検索。fk_から始まる名前を外部キーとして扱いテーブル情報と制約名を取得する。

2. 取得した情報を元にALTER TABLE [テーブル名] DROP FOREIGN KEY [制約名]文を動的に作成して実行する。

drop_foreign_key.sql

DROP PROCEDURE IF EXISTS DropForeignKey;

DELIMITER //
CREATE PROCEDURE DropForeignKey()
BEGIN
  DECLARE _table_name VARCHAR(64);
  DECLARE _constraint_name VARCHAR(64);
  DECLARE _done INT DEFAULT FALSE;
  DECLARE cur CURSOR FOR
    SELECT table_name, constraint_name
    FROM information_schema.key_column_usage
    WHERE constraint_name LIKE 'fk_%';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH FROM cur INTO _table_name, _constraint_name;

    IF _done THEN
      LEAVE read_loop;
    END IF;

    SET @stmt = CONCAT('ALTER TABLE ', _table_name, " DROP FOREIGN KEY ", _constraint_name);
    PREPARE statement FROM @stmt;
    EXECUTE statement;
    DEALLOCATE PREPARE statement;
  END LOOP;
  CLOSE cur;

END//
DELIMITER ;

CALL DropForeignKey;
DROP PROCEDURE DropForeignKey;

実行コマンド

$ mysql -u root -pdummy -D database -e "source drop_foreign_key.sql"

参考文献

• ストアドプロシージャの基本的ななにか
  https://qiita.com/setsuna82001/items/e742338eb93e3a48ba46

• 初心者からのMySQLストアドプロシージャ＆ファンクション入門
  http://proengineer.internous.co.jp/content/columnfeature/7078

• ストアドプロシージャについての公式ドキュメント
  https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html