はじめに
先日MySQLにて、削除した覚えのないレコードが削除されてしまうという事象に遭遇しました。
そのテーブルは他のテーブルを外部キー参照しておりカスケード削除が指定してありました。このせいかと思ったのですが、外部キー参照先のレコードは存在しています。
なんで？と思ってよくよく調べると、参照先のカラムにはユニーク制約がかかっておらず、同じ値を持つレコードが複数あるのでした。そのうちの1レコードを削除するとカスケード削除が走るのです。
検証
検証してみました。環境は MySQL 8.0.39 です。
サンプルテーブル定義
検証に以下2つのテーブルを用意します。
CREATE TABLE parents (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
ref_value VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
KEY idx_ref_value (ref_value) -- インデックスだけあるが UNIQUE ではない
) ENGINE=InnoDB;
CREATE TABLE children (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
ref_value VARCHAR(50) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_children_ref_value FOREIGN KEY (ref_value)
REFERENCES parents (ref_value)
ON DELETE CASCADE -- 親レコードが消えたら子レコードもカスケード削除
) ENGINE=InnoDB;
ポイントは parents.ref_value が UNIQUE でない という点です。
外部キー参照するためにインデックスは必要ですが、一意性は要求されません。
データ投入
INSERT INTO parents (id, ref_value) VALUES
(1, 'aaa'),
(2, 'aaa'), -- 同じ値が重複
(3, 'bbb');
INSERT INTO children (id, ref_value) VALUES
(11, 'aaa'),
(31, 'bbb');
この時点での状況は以下の通り：
- children.id=11 は 'aaa' を参照しているが、parents 側には 'aaa' が 2行存在。
- children.id=31 は 'bbb' を参照している。
検証結果
parents.id=1 を削除してみます。
DELETE FROM parents WHERE id = 1;
すると… children.id=11 も削除されます！
さらに parents.id=2 を削除しても、やはり children.id=11 は削除されます。
つまり「最後の1件が消えたとき」ではなく、対応する親のどれか1行が消えた時点で子が削除される という挙動になります。
公式ドキュメントの記述
ドキュメントにはこう書いてあります。
MySQL requires that the referenced columns be indexed for performance reasons. However, MySQL does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL.
A FOREIGN KEY constraint that references a non-UNIQUE key is not standard SQL but rather an InnoDB extension. The NDB storage engine, on the other hand, requires an explicit unique key (or primary key) on any column referenced as a foreign key.
The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE (including PRIMARY) and NOT NULL keys.
要点を整理すると：
- 参照先列は インデックス必須 だが、UNIQUE である必要はない
- 非ユニーク列を参照するのは 標準SQLに反する InnoDB 独自拡張
- その挙動は 明確に定義されていない（曖昧）
- 実運用では UNIQUE / NOT NULL の列を参照することが推奨
まとめ
- MySQL では非ユニーク列でも外部キー参照は可能。
-
ON DELETE CASCADEを指定すると、「最後の1件が消えたとき」 ではなく 「一致する親が1件でも消えたら子も消える」 という挙動になる。 - これは InnoDB独自の仕様 であり、標準SQLとは異なる。
- 公式も「非ユニーク参照は推奨しない」と明記している。
外部キーを非ユニーク列に張れてしまうのは一見便利な面もありそうですが、想定外のカスケード削除を招く危険があります。
もし同じ値が複数行存在する可能性があるなら、外部キー制約を設計する前に一度立ち止まって考えるのがおすすめです。