MySQLのDROP TABLEで外部キー制約を解除できない

Question

実現したいこと

『外部キー制約がかかったままで、外部キー制約を持つテーブルから先に削除する』を知りたいです。

前提

テスト用にusersテーブルとdatasテーブルをCREATEしました。これを『usersを削除してからdatasを削除する方法』を探しています。

普通にdatasから削除すればいいだけの話ですが、
●なぜSET FOREIGN_KEY_CHECKS = 0;が以下で効かないのか？
●どうすれば効かせられるのか（先にusersを削除できるようになるのか）？
を知りたいということです。

発生している問題・エラーメッセージ

usersを先に削除すると、どうしてもこれが出ます。

#1217 - 子レコードがあります。外部キー制約違反です。

該当のソースコード

これがCREATEです。

CREATE TABLE users (
    id        INT          UNSIGNED NOT NULL AUTO_INCREMENT,
    user_name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE datas (
    id        INT          UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id   INT          UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT foreifn_key FOREIGN KEY (user_id) REFERENCES users(id)
);

上のCREATEをこう削除するとエラーです。

DROP TABLE users;
DROP TABLE datas;

試したこと

困ったことに、このように試しても同じエラーなのです。なぜなのでしょうか？

このようにSET FOREIGN_KEY_CHECKS = 0;を指定すれば、『外部キー制約がかかったままで、外部キー制約を持つテーブルから先に削除する』ことができるのではないのでしょうか？

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE users;
DROP TABLE datas;

テスト用にCREATEしただけなのでとっとと削除したいのですが、いちいち外部キー制約の関係性を確認して削除をかけるのが手間に感じます。
今後この手間を感じたくないので、制約関係なしに削除する方法を知りたいです。

補足情報（FW/ツールのバージョンなど）

MySQLは「5.7」で、phpMyAdminのSQLタブで実行しています。

追記 (2023/06/09 10:47)

yambejp様のご回答に高評価がありますが未解決なので追記します。
下記の画像のように、yambejp様のご回答ですと惜しくもdatasの削除のみという状況で、エラーは同様のものが表示されます。

引き続き有識者の皆様からのご回答をお待ちしております。
何卒よろしくお願い申し上げます。

Comments

[maisumakun]

> 普通にdatasから削除すればいいだけの話ですが

そうではなくusersを先に削除したい、特別な事情はあるのでしょうか？

[kikukiku]

仮に上記が成功したとしても、
削除したテーブルに関連する外部キーは自動的に削除されるようなので
上記にこだわるのではなく
外部キーを削除してからテーブルを削除したらどうでしょう。

[origa3]

maisumakunさん、「dataを先に削除しなければならない」ことの確認の手間を省きたいのです。FOREIGN_KEY_CHECKS = 0 を指定すればusersを先に削除できると思ったのにできない…なぜ…という疑問です。

[origa3]

kikukikuさん、もちろん丁寧に外部キーを削除してからテーブルを削除してもいいのですが、そういった方法ではなく、外部キーを無視して削除する方法が知りたいのです。その方法として検索にひっかかったのが「試したこと」にある FOREIGN_KEY_CHECKS なのですが、これが効かない理由が知りたいのです。

[maisumakun]

ストレージエンジンは何を使っていますか？

[origa3]

maisumakunさん、show engines; しました。Support カラムが DEFAULT のレコードの、Engine カラムは InnoDB でした。

ただ、そのレコードの Comment カラムの値は "Supports transactions, row-level locking, and fore..." のように三点リーダーで省略されてしまい、すべてを見ることはできませんでした。

これで必要な情報の提示ができているのか…勉強不足で申し訳ございません。

[logres_Fan]

　SHOW ENGINE INNODB STATUSで外部キー関連エラーのログが見れるならば確認してみるのは、どうでしょう？
　あまり余地が無いけれど、コードの単語を足したり引いたりして、再現する・しないを繰り返すくらいしか・・・。

Answer 1

yamabejp さんの回答:

また、drop tableは複数一気に消せるので
drop table users,datas
とすればいいだけですよね？

そのはずなのですが、そのDROP TABLE文を実行したときの挙動に問題があり、改善されたようです。

• Checking for foreign key relationships by DROP TABLE and DROP DATABASE was improved. The check now takes place before any tables are deleted, so that in the event of a problem the statement fails before making any changes. Parent and child tables now can be dropped in arbitrary order, as long as they are dropped by the same DROP TABLE statement. In addition, error reporting was improved for attempts to drop a parent table without dropping a child table. (Bug #27821060, Bug #17564464, Bug #70531, Bug #22359539, Bug #79610)

(引用元: MySQL 8.0 Release Notes / Changes in MySQL 8.0.12 (2018-07-27, General Availability), Functionality Added or Changed.)

修正の内容はこちらで確認できます。

• github.com / mysql/mysql-server@d408eb3

そういうわけですから、MySQL 8.0.12以降を使えば質問者さんの望むものに近い挙動を実現できるのではないでしょうか。逆に言うと、MySQL 5.7では実現できないということになりますね。

Comments

[origa3]

ありがとうございます。明確なソースをご提示頂けたおかげでMySQL 5.7では実現できないことが判明しました。

Answer 2

なにを問題視しているかわかりませんが、そもそも外部キー制約をつけている時点で
テーブルはusersを先につくってdatasを後からつくらなくてはいけません。
それは理解できるのに、消す順番は納得がいかないというのは合理的ではありません。

また、drop tableは複数一気に消せるので
drop table users,datas
とすればいいだけですよね？

Comments

[origa3]

一風変わった合理性のジャッジをされますね。あとdrop table users,datasですが、せっかく教えて頂いたのですが質問と同様のエラーになりました。

[origa3]

ちなみにdrop table users,datasの場合、質問と同様のエラーは出ますが、datasは削除されていました。
質問の3行だと、usersもdatasも削除されません。（今一度確認致しました。）

[yambejp]

> 一風変わった合理性のジャッジをされますね

先にマスターテーブルを作らないと外部参照設定できない＝外部参照しているならマスターテーブルを削除ができないのはあたりまえというロジックが「変わった合理性」と感じるなら、噛み合わないのでこれ以上補足はいたしません。

なお5.7環境はもう手元にないので、なんと言えませんが8レベルまで上げれば良いかもしれません

[origa3]

なるほど、バージョンですか。ありがとうございます。参考にさせていただきます。

[origa3]

その後、バージョン更新以外にはいかがでしょうか？
アプローチの方向性だとか、試してみるべき内容などにお心あたりございませんでしょうか？

Answer 3

Oracle Database での例ですが、FOREIGN KEY 外部キー(外部参照キー)を実行して、何のために外部キーがあるのか？って用途を確認されては？

Comments

[origa3]

なんのために SET FOREIGN_KEY_CHECKS = 0; があるのでしょうか…

[Orlofsky]

SET FOREIGN_KEY_CHECKS = 0;は外部キーを一時的に無効にする機能だけど、DROP TABLE は外部キーを削除する機能ではないから、外部キーをDROPしては？
https://qiita.com/petitviolet/items/6e8d97b600721dcf7dc0

Answer 4

このように試しても

その3行は、どのような方法で実行しましたか？

1行ずつ実行して、それぞれの行が別なセッションになっていることはないでしょうか。

Comments

[origa3]

phpMyAdminのSQLタブをクリックするとテキストエリアが表示されますので、そこに3行すべて一括入力し、実行をクリックしました。

[origa3]

間違いなく3行を一括実行したこと、質問に画像で追記させて頂きました。
また思い当たることございましたら引き続きよろしくお願い申し上げます。