Infrastructure at your Service

Elisa Usai

MySQL – Foreign keys and “Cannot delete or update a parent row: a foreign key constraint fails” error

As you know, foreign keys establish a sort of relationship between 2 tables. MySQL requires InnoDB storage engine to support foreign keys.

In our example, we have the following parent table in a MySQL 5.7.21 server:

mysqld3-(root@localhost) [sakila]> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 

and a foreign key is defined on the child table by using the “FOREIGN KEY… REFERENCES” syntax:

mysqld3-(root@localhost) [sakila]> show create table film_actor\G
*************************** 1. row ***************************
       Table: film_actor
Create Table: CREATE TABLE `film_actor` (
  `actor_id` smallint(5) unsigned NOT NULL,
  `film_id` smallint(5) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`,`film_id`),
  KEY `idx_fk_film_id` (`film_id`),
  CONSTRAINT `fk_film_actor_actor` FOREIGN KEY (`actor_id`) REFERENCES `actor` (`actor_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_actor_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

 

The “ON UPDATE CASCADE” clause means that if we update values in the parent table (‘actor’ in our example),

mysqld3-(root@localhost) [sakila]> select * from actor where actor_id=1;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)
mysqld3-(root@localhost) [sakila]> select * from film_actor where actor_id=1;
+----------+---------+---------------------+
| actor_id | film_id | last_update         |
+----------+---------+---------------------+
|        1 |       1 | 2006-02-15 05:05:03 |
|        1 |      23 | 2006-02-15 05:05:03 |
|        1 |      25 | 2006-02-15 05:05:03 |
|        1 |     106 | 2006-02-15 05:05:03 |
|        1 |     140 | 2006-02-15 05:05:03 |
|        1 |     166 | 2006-02-15 05:05:03 |
|        1 |     277 | 2006-02-15 05:05:03 |
|        1 |     361 | 2006-02-15 05:05:03 |
|        1 |     438 | 2006-02-15 05:05:03 |
|        1 |     499 | 2006-02-15 05:05:03 |
|        1 |     506 | 2006-02-15 05:05:03 |
|        1 |     509 | 2006-02-15 05:05:03 |
|        1 |     605 | 2006-02-15 05:05:03 |
|        1 |     635 | 2006-02-15 05:05:03 |
|        1 |     749 | 2006-02-15 05:05:03 |
|        1 |     832 | 2006-02-15 05:05:03 |
|        1 |     939 | 2006-02-15 05:05:03 |
|        1 |     970 | 2006-02-15 05:05:03 |
|        1 |     980 | 2006-02-15 05:05:03 |
+----------+---------+---------------------+
19 rows in set (0.00 sec)
mysqld3-(root@localhost) [sakila]> update actor set actor_id=300 where actor_id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

then this will cause an automatic update of the matching rows in the child table (‘film_actor’):

mysqld3-(root@localhost) [sakila]> select * from actor where actor_id=1;
Empty set (0.00 sec)
mysqld3-(root@localhost) [sakila]> select * from film_actor where actor_id=1;
Empty set (0.00 sec)
mysqld3-(root@localhost) [sakila]> select * from actor where actor_id=300;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|      300 | PENELOPE   | GUINESS   | 2018-02-07 15:41:45 |
+----------+------------+-----------+---------------------+
1 row in set (0.00 sec)
mysqld3-(root@localhost) [sakila]> select * from film_actor where actor_id=300;
+----------+---------+---------------------+
| actor_id | film_id | last_update         |
+----------+---------+---------------------+
|      300 |       1 | 2006-02-15 05:05:03 |
|      300 |      23 | 2006-02-15 05:05:03 |
|      300 |      25 | 2006-02-15 05:05:03 |
|      300 |     106 | 2006-02-15 05:05:03 |
|      300 |     140 | 2006-02-15 05:05:03 |
|      300 |     166 | 2006-02-15 05:05:03 |
|      300 |     277 | 2006-02-15 05:05:03 |
|      300 |     361 | 2006-02-15 05:05:03 |
|      300 |     438 | 2006-02-15 05:05:03 |
|      300 |     499 | 2006-02-15 05:05:03 |
|      300 |     506 | 2006-02-15 05:05:03 |
|      300 |     509 | 2006-02-15 05:05:03 |
|      300 |     605 | 2006-02-15 05:05:03 |
|      300 |     635 | 2006-02-15 05:05:03 |
|      300 |     749 | 2006-02-15 05:05:03 |
|      300 |     832 | 2006-02-15 05:05:03 |
|      300 |     939 | 2006-02-15 05:05:03 |
|      300 |     970 | 2006-02-15 05:05:03 |
|      300 |     980 | 2006-02-15 05:05:03 |
+----------+---------+---------------------+

 

Sometimes, when we must drop an InnoDB table in MySQL, we could encounter the following error due to foreign keys:

mysqld3-(root@localhost) [sakila]> drop table actor;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

 

In this example, the ‘actor’ table is referenced by the ‘film_actor’ one:

mysqld3-(root@localhost) [sakila]> SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
    -> FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    -> WHERE REFERENCED_TABLE_SCHEMA = 'sakila'
    -> AND REFERENCED_TABLE_NAME = 'actor';
+------------+-------------+---------------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME     | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+---------------------+-----------------------+------------------------+
| film_actor | actor_id    | fk_film_actor_actor | actor                 | actor_id               |
+------------+-------------+---------------------+-----------------------+------------------------+
1 row in set (0.01 sec)

 

This foreign key constraint let data being consistent over different tables and that’s also the reason why we could not drop the parent table.
We can find this same information and the error cause displaying the state of the InnoDB storage engine through the “SHOW ENGINE INNODB STATUS” command:

mysqld3-(root@localhost) [sakila]> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2018-02-07 15:44:34 0x7fb734174700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 46 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 84 srv_active, 0 srv_shutdown, 85720 srv_idle
srv_master_thread log flush and writes: 85803
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1607
OS WAIT ARRAY INFO: signal count 1552
RW-shared spins 0, rounds 757, OS waits 384
RW-excl spins 0, rounds 342, OS waits 11
RW-sx spins 2, rounds 60, OS waits 1
Spin rounds per wait: 757.00 RW-shared, 342.00 RW-excl, 30.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-02-07 15:42:45 0x7fb734174700  Cannot drop table `sakila`.`actor`
because it is referenced by `sakila`.`film_actor`
...

 

To avoid these constraint errors during table deletion, there are different solutions:
– Drop table in the correct order (child table first, parent table as the last one)
– In case of a loop in foreign keys, remove this loop and redefine tables structure before dropping tables
– You can also temporarily set “FOREIGN_KEY_CHECKS=0”, drop the table and put again “FOREIGN_KEY_CHECKS=1”, but I don’t recommend using this method (especially in a production environment!)

Leave a Reply

Elisa Usai
Elisa Usai