🌻

MySQL: 外部キーで参照されているテーブルはTRUNCATE できない

2023/01/31に公開

検証した環境

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 (`order_sync_local`.`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