概要
MySQLの学習でサンプルデータを修正・再投入した際、AUTO_INCREMENTのidがズレてしまい、外部キー制約エラーが発生した。その原因と対処法をまとめる。
本記事を書くに至ったプロジェクトのリポジトリは以下。
https://github.com/songgeye/internet-tv-database
発生した問題
サンプルデータを修正して再投入した際に以下のエラーが発生した。
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
原因
データを部分的に削除・再投入した場合、AUTO_INCREMENTは直前の最大値から続きの番号が振られる。
例えば以下のようなケースで発生した。
-
time_slotsのデータを一部修正して再投入した -
slot_episode_viewsのtime_slot_idが修正前のidを参照していた - AUTO_INCREMENTがリセットされていないため、idがズレてしまった
-- 修正前のtime_slots
id=3 ドラマch / 半沢直樹 '2026-05-10 21:00:00'
-- 修正後のtime_slots（日付を変更して再投入）
id=8 ドラマch / 半沢直樹 '2026-05-11 21:00:00' ← idが変わってしまった
-- slot_episode_viewsは古いid=3を参照したままのためエラーになる
(3, 7, 18000) -- time_slot_id=3はもう存在しない
対処法
1. 全データを削除する
外部キー制約があるため、参照している側から順に削除する。
削除順序はINSERT順序の逆になる。
以下はsample.sqlの投入順序。
-- 投入順序（参照される側から）
genres → programs → channels → program_genres → seasons → episodes → time_slots → slot_episode_views
そのため削除順序はこうなる。
-- 削除順序（参照している側から）
DELETE FROM slot_episode_views;
DELETE FROM time_slots;
DELETE FROM episodes;
DELETE FROM seasons;
DELETE FROM program_genres;
DELETE FROM channels;
DELETE FROM programs;
DELETE FROM genres;
2. AUTO_INCREMENTをリセットする
ALTER TABLE slot_episode_views AUTO_INCREMENT = 1;
ALTER TABLE time_slots AUTO_INCREMENT = 1;
ALTER TABLE program_genres AUTO_INCREMENT = 1;
ALTER TABLE episodes AUTO_INCREMENT = 1;
ALTER TABLE seasons AUTO_INCREMENT = 1;
ALTER TABLE programs AUTO_INCREMENT = 1;
ALTER TABLE channels AUTO_INCREMENT = 1;
ALTER TABLE genres AUTO_INCREMENT = 1;
3. サンプルデータを再投入する
source /path/to/sample.sql
教訓
- サンプルデータを修正する際は、データの削除とAUTO_INCREMENTのリセットをセットで行う
- 外部キーを持つテーブルのidが変わる場合は、参照しているテーブルのidも合わせて修正が必要
- データの削除・投入順序は外部キーの依存関係に従う
- 削除：参照している側から削除（子→親）
- 投入：参照される側から投入（親→子）