MySQLのテーブル削除 ― DROP・TRUNCATE・DELETEの違いと外部キーのERROR 3730

※この記事にはアフィリエイト広告（プロモーション）が含まれます。

この記事の出力は MySQL 8.4.11（Ubuntu 26.04、InnoDB・innodb_file_per_table=ON）で採りました。

まず見る（結論）

DROP TABLE t;                 -- テーブルごと消す（定義も消える）
DROP TABLE IF EXISTS t;       -- 無くてもエラーにしない（Note 1051）
TRUNCATE TABLE t;             -- 中身を全部消す
DELETE FROM t;                -- 中身を全部消す
DELETE FROM t WHERE id = 3;   -- 条件に合う行だけ

外部キーで削除できないとき

子テーブル c が親テーブル p を外部キーで参照している状態で試しました。

mysql> DROP TABLE p;
ERROR 3730 (HY000): Cannot drop table 'p' referenced by a foreign key constraint 'c_ibfk_1' on table 'c'.

mysql> TRUNCATE TABLE p;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`fsv`.`c`, CONSTRAINT `c_ibfk_1`)

mysql> DELETE FROM p;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (...)

子の行から参照されていない行（DELETE FROM p WHERE id = 3）は消せました。

直し方

• 両方消すなら1文で並べる。DROP TABLE c, p; と DROP TABLE p, c; のどちらも通った
• 親だけ消すなら子の外部キーを先に外す。ALTER TABLE c DROP FOREIGN KEY c_ibfk_1;（制約名は ERROR 3730 のメッセージに出る）
• どの表が参照しているかは information_schema.KEY_COLUMN_USAGE の REFERENCED_TABLE_NAME で引く

SELECT TABLE_NAME, CONSTRAINT_NAME
  FROM information_schema.KEY_COLUMN_USAGE
 WHERE REFERENCED_TABLE_SCHEMA = 'mydb' AND REFERENCED_TABLE_NAME = 'p';

FOREIGN_KEY_CHECKS = 0 で消した後

SET FOREIGN_KEY_CHECKS = 0; DROP TABLE p; は通ります。ただし子の c には REFERENCES `p` (`id`) の制約が残ったままで、その後の INSERT INTO c は ERROR 1452 で失敗しました。親が無いので、どの値を入れても通りません。外部キーのエラーは ERROR 1452・1451 の記事に書きました。

AUTO_INCREMENT の戻り方

INSERT INTO ai (v) VALUES (1),(2),(3);   -- id 1, 2, 3
DELETE FROM ai;  INSERT INTO ai (v) VALUES (4);   -- id 4
TRUNCATE TABLE ai;  INSERT INTO ai (v) VALUES (5);   -- id 1

DELETE の後に番号を 1 から振り直すなら ALTER TABLE ai AUTO_INCREMENT = 1; を実行します。表が空ならこれで次の行は 1 になりました。

ROLLBACK できるか

• START TRANSACTION; DELETE FROM ai; ROLLBACK; … 3行が戻った
• START TRANSACTION; TRUNCATE TABLE ai; ROLLBACK; … 0行のまま。TRUNCATE はその場で確定する
• START TRANSACTION; INSERT ...; DROP TABLE ai2; ROLLBACK; … DROP の前の INSERT も確定していた。DROP TABLE が実行前にそれまでの変更をコミットするため

消した件数は DELETE なら ROW_COUNT() で取れますが、TRUNCATE の後は 0 でした。

ディスクの空き

.ibd の数字は20万行の表で測りました。DELETE の後に空きを取り戻す手順は DELETE してもディスクが減らない記事に書きました。

終わらないとき（メタデータロック）

別の接続が START TRANSACTION; SELECT * FROM lk; のままにしていると、DROP TABLE lk も TRUNCATE TABLE lk も待たされます。lock_wait_timeout（既定は1年）を過ぎると ERROR 1205 Lock wait timeout exceeded になります。DELETE FROM lk は SELECT と競合せず、すぐ終わりました。

待っている相手は SHOW PROCESSLIST の Waiting for table metadata lock で分かります。探し方は ALTER TABLE の記事と Lock wait timeout exceeded の記事に書きました。

権限

SELECT・INSERT・DELETE だけを持つユーザーでは、DELETE FROM pt は通りました。TRUNCATE TABLE pt と DROP TABLE pt は ERROR 1142 DROP command denied to user になりました。権限の付け方は ユーザー作成と権限付与の記事に書きました。

無いテーブル・データベース

• DROP TABLE nosuch … ERROR 1051 (42S02): Unknown table 'fsv.nosuch'
• DROP DATABASE nosuchdb … ERROR 1008 (HY000): Can't drop database 'nosuchdb'; database doesn't exist。IF EXISTS を付けると通る

関連: MySQLでテーブル一覧を表示する ／ MySQLのUPDATE文 ／ mysqldumpの使い方