[#MDEV-17440] ON DELETE CASCADE does not always delete rows even if it could

When defining foreign key constraints with ON DELETE clauses it is possible to create a situation where MariaDB does not automatically delete data even if it could. This forces the user to perform the deletion manually.

mysql> CREATE TABLE `a` (

    ->   `id` INT UNSIGNED PRIMARY KEY

    -> ) ENGINE = INNODB;

Query OK, 0 rows affected (0,06 sec)

mysql>

mysql> CREATE TABLE `b` (

    ->   `id` INT UNSIGNED PRIMARY KEY,

    ->   `a_id` INT UNSIGNED NULL,

    ->   CONSTRAINT FOREIGN KEY `a_b_fk` (`a_id`) REFERENCES `a` (`id`)

    ->     ON DELETE CASCADE

    ->     ON UPDATE CASCADE

    -> ) ENGINE = INNODB;

Query OK, 0 rows affected (0,07 sec)

mysql>

mysql> CREATE TABLE `c` (

    ->   `id` INT UNSIGNED PRIMARY KEY,

    ->   `a_id` INT UNSIGNED NULL,

    ->   `b_id` INT UNSIGNED NULL,

    ->   CONSTRAINT FOREIGN KEY `a_c_fk` (`a_id`) REFERENCES `a` (`id`)

    ->     ON DELETE CASCADE

    ->     ON UPDATE CASCADE,

    ->   CONSTRAINT FOREIGN KEY `b_c_fk` (`b_id`) REFERENCES `b` (`id`)

    ->     ON DELETE RESTRICT

    ->     ON UPDATE CASCADE

    -> ) ENGINE = INNODB;

Query OK, 0 rows affected (0,08 sec)

mysql> INSERT INTO `a` VALUES (1);

Query OK, 1 row affected (0,01 sec)

mysql> INSERT INTO `b` VALUES (1,1);

Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO `c` VALUES (1,1,1);

Query OK, 1 row affected (0,01 sec)

mysql> SELECT * FROM `a`;

+----+

| id |

+----+

|  1 |

+----+

1 row in set (0,00 sec)

mysql> SELECT * FROM `b`;

+----+------+

| id | a_id |

+----+------+

|  1 |    1 |

+----+------+

1 row in set (0,00 sec)

mysql> SELECT * FROM `c`;

+----+------+------+

| id | a_id | b_id |

+----+------+------+

|  1 |    1 |    1 |

+----+------+------+

1 row in set (0,00 sec)

mysql> -- will be rejected

mysql> DELETE FROM `b` WHERE `id` = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`crmi_frontend_dev`.`c`, CONSTRAINT `b_c_fk` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`) ON UPDATE CASCADE)

mysql> SELECT * FROM `a`;

+----+

| id |

+----+

|  1 |

+----+

1 row in set (0,00 sec)

mysql> SELECT * FROM `b`;

+----+------+

| id | a_id |

+----+------+

|  1 |    1 |

+----+------+

1 row in set (0,00 sec)

mysql> SELECT * FROM `c`;

+----+------+------+

| id | a_id | b_id |

+----+------+------+

|  1 |    1 |    1 |

+----+------+------+

1 row in set (0,00 sec)

mysql> -- will also be rejected, but shouldn't

mysql> DELETE FROM `a` WHERE `id` = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`crmi_frontend_dev`.`c`, CONSTRAINT `b_c_fk` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`) ON UPDATE CASCADE)

mysql> SELECT * FROM `a`;

+----+

| id |

+----+

|  1 |

+----+

1 row in set (0,00 sec)

mysql> SELECT * FROM `b`;

+----+------+

| id | a_id |

+----+------+

|  1 |    1 |

+----+------+

1 row in set (0,00 sec)

mysql> SELECT * FROM `c`;

+----+------+------+

| id | a_id | b_id |

+----+------+------+

|  1 |    1 |    1 |

+----+------+------+

1 row in set (0,00 sec)

When deleting a row from a it also gets rejected, because the first dependent row to be deleted is in b and deletions from b are rejected. But this is not the only order in which referencing rows can be deleted. If MariaDB would first delete the row in c then the row in b also could be deleted.

So it seems MariaDB processes the keys depth-first. In this case breadth-first would have been better. But it wouldn't always work. The best algorithm would be probably to build a dependency graph and then delete those rows first that don't have dependencies. Then dependent rows, then their dependents and so no. Circular references would probably stop the whole process, and the deletion would be rejected.

[MDEV-17440] ON DELETE CASCADE does not always delete rows even if it could Created: 2018-10-12 Updated: 2023-10-08
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Data Manipulation - Delete, Storage Engine - InnoDB
Affects Version/s:	5.5, 10.0, 10.1, 10.2, 10.3
Fix Version/s:	10.4

[MDEV-17440] ON DELETE CASCADE does not always delete rows even if it could Created: 2018-10-12 Updated: 2023-10-08