Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
Description
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.
Consider following example:
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)
|
Both tables b and c reference a.
Deletions from b get rejected because of the foreign key in c.
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.
Attachments
Issue Links
- is blocked by
-
MDEV-22361 Cross-engine foreign keys support
-
- Open
-