外部キー制約があるDBテーブル内のデータを削除する手順

はじめに

開発中に既にデータが入っているDBテーブルに外部キーを追加したかったのですが、「既にデータがあると外部キーを持っていない過去のデータが存在してしまうことになるから追加できないよ！」とエラーを返してしまいました。

じゃあDBテーブル内のデータを削除しよう!とMySQLに入ってコマンドを叩いたところ「外部キーを持ってるデータはtruncateできないよ！」とまたもエラーが返ってきました。(削除しようとしているテーブルにも既に外部キーが存在しています)

mysql> truncate table quizzes;

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`~`.`~`, CONSTRAINT `~`)

外部キーが存在するDBテーブルのデータを削除する方法を毎回調べるのも無駄なので備忘録としてまとめます。

外部キー制約を一時的に解除する

外部キー制約をもつDBテーブルのデータを削除するためには、コマンドを叩いて一時的に外部キー制約を解除する必要があります。
下記のコマンドで、外部キー制約を解除したり再び適用させることができます。
(解除コマンドを叩くと全テーブルの外部キー制約が解除されるみたいです。)

#外部キー制約を解除
mysql> set foreign_key_checks = 0;
#外部キー制約を再び適用
mysql> set foreign_key_checks = 0;

テーブル内のデータを削除する手順が下記になります。

#外部キー制約で削除が失敗
mysql> truncate table quizzes;

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`~`.`~`, CONSTRAINT `~`)

#外部キー制約を解除
mysql> set foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

#削除成功
mysql> truncate table quizzes;
Query OK, 0 rows affected (0.09 sec)

#しっかり空になってる
mysql> select * from quizzes\G
Empty set (0.00 sec)

#忘れずに外部キー制約を再び適用
mysql> set foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)

終わりに

無事にデータを削除してカラムを追加することができました。
コマンドは実際に触らないと慣れないなぁと思いました。
怖がらずに新しいコマンドを使っていこうと思います。

参考