MySQLで親テーブルの行を削除するとどうなる？外部キー制約によるエラーの原因と対処法

親テーブルの行を削除するとどうなるか

MySQLでは、親テーブルから行を削除しようとした際、その行のデータが子テーブルから参照されている場合、外部キー（FOREIGN KEY）制約の違反によりエラーが発生し、削除は拒否されます。これは、データベースの参照整合性（リレーショナルデータの一貫性）を保つための重要な仕組みです。

この動作は、「customer」（親テーブル）と「orders」（子テーブル）という2つのテーブルを例にすると分かりやすくなります。子テーブル「orders」で参照されている行は、親テーブル「customer」から削除することができません。

テーブルの内容を確認する

まず、親テーブルと子テーブルのデータを確認してみましょう。

mysql> Select * from Customer;
+----+--------+
| id | name   |
+----+--------+
| 1  | Gaurav |
| 2  | Raman  |
| 3  | Harshit|
| 4  | Aarav  |
+----+--------+
4 rows in set (0.00 sec)

mysql> Select * from orders;
+----------+----------+------+
| order_id | product  | id   |
+----------+----------+------+
| 100      | Notebook | 1    |
| 110      | Pen      | 1    |
| 120      | Book     | 2    |
| 130      | Charts   | 2    |
+----------+----------+------+
4 rows in set (0.00 sec)

削除を試みるとエラーが発生する

上記の例では、id = 1 および id = 2 の顧客データが子テーブル「orders」から参照されています。これらの行を親テーブル「customer」から削除しようとすると、外部キー制約に違反するため、以下のようなエラーが出力されます。

mysql> Delete from customer where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`query`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`id`)REFERENCES `customer` (`id`))

mysql> Delete from customer where id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`query`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`id`)REFERENCES `customer` (`id`))

エラーの意味と主な対処法

「ERROR 1451」は、参照整合性を維持するためにMySQLが削除操作を自動的にブロックしたことを示しています。子テーブルに存在しない親データを作らないことで、データベース全体の整合性を守っているのです。このエラーを解決して親テーブルの行を削除したい場合は、以下のいずれかの方法を採用します。

• 子テーブルの関連行を先に削除する： 親テーブルの行を削除する前に、その行を参照している子テーブル「orders」側の行をあらかじめ削除しておきます。最も基本的な対処法です。
• ON DELETE CASCADE を設定する： 外部キー定義に「ON DELETE CASCADE」オプションを指定しておくと、親テーブルの行が削除されたタイミングで、それを参照する子テーブルの行も自動的に削除されます。
• ON DELETE SET NULL を設定する： 親の行が削除された際に、子テーブルの外部キー列をNULLに更新する方式です。注文履歴など、子データ自体は残したい場合に有効です。

どの方法を選ぶべきかは、アプリケーションの要件によって異なります。誤って必要なデータを消さないよう、運用方針に合わせて適切な外部キーオプションを設計することが大切です。