What happens if I delete a row from the MySQL parent table?

When the row is deleted from the parent table, if the row's data is used in the child table, MySQL will throw an error because the FOREIGN KEY constraint fails. It can be understood using the examples of the two tables "customer" and "orders". Here, "customer" is the parent table and "orders" is the child table. We cannot delete rows from the "customer" table that are used in the subtable "orders". This can be demonstrated by deleting values ​​from the parent table as shown below -

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)

Now, suppose we try to delete rows with id = 1 or id = 2 from the parent table "customer" (since the child table uses these two rows), MySQL will throw the following error because the foreign key constraint failed.

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`))

The above is the detailed content of What happens if I delete a row from the MySQL parent table?. For more information, please follow other related articles on the PHP Chinese website!