検証した環境
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)
検証内容
以下2つのテーブルを作る。
CREATE TABLE `parent` (
`id` int NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `child` (
`id` int NOT NULL,
`parent_id` int NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (parent_id) REFERENCES parent(id)
) ENGINE=InnoDB;
child テーブルは parent テーブルへの外部キー参照がある。
mysql> CREATE TABLE `parent` (
-> `id` int NOT NULL,
-> `name` varchar(255) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE `child` (
-> `id` int NOT NULL,
-> `parent_id` int NOT NULL,
-> `name` varchar(255) NOT NULL,
-> PRIMARY KEY (`id`),
-> FOREIGN KEY (parent_id) REFERENCES parent(id)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
CREATE TABLE したばかりなので、何もデータは入っていない。
mysql> SELECT count(*) FROM parent;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.04 sec)
mysql> SELECT count(*) FROM child;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.01 sec)
ここでおもむろに TRUNCATE TABLE する。
mysql> TRUNCATE TABLE parent;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`exampledb`.`child`, CONSTRAINT `child_ibfk_1`)
なんと、外部キー制約でエラーになる。（データが1件も無いのに）
子テーブルは当然だがTRUNCATEできる。
mysql> TRUNCATE TABLE child;
Query OK, 0 rows affected (0.04 sec)
考察
MySQLマニュアルちゃんと読めば書いてありました。
高パフォーマンスを実現するために、TRUNCATE TABLE はデータを削除する DML メソッドをバイパスします。 したがって、ON DELETE トリガーは起動せず、親子外部キー関係を持つ InnoDB テーブルに対しては実行できず、DML 操作のようにロールバックできません。
TRUNCATE を 「全件DELETEを高速にやるやつ」くらいのイメージでいるとうっかりこの罠にはまりますね。
上記ページの別記述にもありますが
切り捨て操作はテーブルを削除して再作成する
ということなので、TRUNCATEする時点で一時的にテーブルが存在しなくなると考えると
外部キーで参照されているテーブルがTRUNCATEされるのは、参照先テーブルが無くなるのはまずいのでそりゃエラーになりますよね。
先日仕事で見事にはまったので備忘まで。
追記
TRUNCATE TABLE する前に SET foreign_key_checks = 0; すると （外部キー制約を無効化できるので）TRUNCATEできます。
戻すときは SET foreign_key_checks = 1;
Discussion