MySQL Bugs: #100023: Foreign Key Update Cascade FAILS on 2+ Tables Where BOTH are Parent and Child

Bug #100023	Foreign Key Update Cascade FAILS on 2+ Tables Where BOTH are Parent and Child
Submitted:	28 Jun 2020 16:31	Modified:	1 Jul 2020 12:00
Reporter:	Brad Lanier	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Foreign Key Update Cascade

Description:
MySQL 8.0 Reference Manual Section 13.1.20.5 FOREIGN KEY Constraints "Referential Actions" under "Cascade" states.... "If a FOREIGN KEY clause is defined on both tables in a foreign key relationship, making both tables a parent and child, an ON UPDATE CASCADE or ON DELETE CASCADE subclause defined for one FOREIGN KEY clause must be defined for the other in order for cascading operations to succeed. If an ON UPDATE CASCADE or ON DELETE CASCADE subclause is only defined for one FOREIGN KEY clause, cascading operations fail with an error."

With the case which follows, the UPDATE FAILS. This case seems to follow the guidelines quoted above in the reference manual.

How to repeat:
Run the following script:
use test;
create table `roleType` (
	`name` varchar(25) NOT NULL,
	`baseName` varchar(25) NOT NULL,
	PRIMARY KEY(`name`),
	KEY `baseName` (`baseName`));
create table `baseRoleType` (
	`name` varchar(25) NOT NULL,
	PRIMARY KEY (`name`),
	CONSTRAINT `baseRoleType.name_roleType.name` FOREIGN KEY (`name`) REFERENCES `roletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE);
alter table `roleType`
	ADD CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseroletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE;
SET @@foreign_key_checks = FALSE;
insert into baseRoleType (name) values ("Person");
SET @@foreign_key_checks = TRUE;
insert into RoleType (name, baseName) values ("Person","Person");
update roleType set name = "Person" where name = "Persons";

The UPDATE FAILS with the following error: Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`test`.`roletype`, CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseroletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE)

Thank you for the bug report. I couldn't repeat:
Database changed
mysql> create table `roleType` (
    -> `name` varchar(25) NOT NULL,
    -> `baseName` varchar(25) NOT NULL,
    -> PRIMARY KEY(`name`),
    -> KEY `baseName` (`baseName`));
Query OK, 0 rows affected (0.03 sec)

mysql> create table `baseRoleType` (
    -> `name` varchar(25) NOT NULL,
    -> PRIMARY KEY (`name`),
    -> CONSTRAINT `baseRoleType.name_roleType.name` FOREIGN KEY (`name`) REFERENCES `roletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE);
Query OK, 0 rows affected (0.05 sec)

mysql> alter table `roleType`
    -> ADD CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseroletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SET @@foreign_key_checks = FALSE;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into baseRoleType (name) values ("Person");
Query OK, 1 row affected (0.01 sec)

mysql> SET @@foreign_key_checks = TRUE;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into RoleType (name, baseName) values ("Person","Person");
Query OK, 1 row affected (0.01 sec)

mysql> update roleType set name = "Person" where name = "Persons";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> show variables like "%version%";
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.20                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.20                        |
| version_comment          | MySQL Community Server - GPL  |
| version_compile_machine  | x86_64                        |
| version_compile_os       | Win64                         |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+
11 rows in set (0.01 sec)

mysql>

If you have additional information or something I missed please comment here, Thanks.

Checked on Linux, see the error in the test case:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0,17 sec)

mysql> USE test
Database changed
mysql> create table `roleType` (
    -> `name` varchar(25) NOT NULL,
    -> `baseName` varchar(25) NOT NULL,
    -> PRIMARY KEY(`name`),
    -> KEY `baseName` (`baseName`));
Query OK, 0 rows affected (1,04 sec)

mysql> create table `baseRoleType` (
    -> `name` varchar(25) NOT NULL,
    -> PRIMARY KEY (`name`),
    -> CONSTRAINT `baseRoleType.name_roleType.name` FOREIGN KEY (`name`) REFERENCES `roletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE);
ERROR 1824 (HY000): Failed to open the referenced table 'roletype'
mysql> create table `baseRoleType` ( `name` varchar(25) NOT NULL, PRIMARY KEY (`name`), CONSTRAINT `baseRoleType.name_roleType.name` FOREIGN KEY (`name`) REFERENCES `roleType` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE);
Query OK, 0 rows affected (0,64 sec)

mysql> alter table `roleType`
    -> ADD CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseroletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE;
ERROR 1824 (HY000): Failed to open the referenced table 'baseroletype'
mysql> alter table `roleType` ADD CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseroleType` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE;
ERROR 1824 (HY000): Failed to open the referenced table 'baseroleType'
mysql> alter table `roleType` ADD CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseRoleType` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE;
Query OK, 0 rows affected (1,93 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SET @@foreign_key_checks = FALSE;
Query OK, 0 rows affected (0,00 sec)

mysql> insert into baseRoleType (name) values ("Person");
Query OK, 1 row affected (0,10 sec)

mysql> SET @@foreign_key_checks = TRUE;
Query OK, 0 rows affected (0,00 sec)

mysql> insert into RoleType (name, baseName) values ("Person","Person");
ERROR 1146 (42S02): Table 'test.RoleType' doesn't exist
mysql> insert into roleType (name, baseName) values ("Person","Person");
Query OK, 1 row affected (0,07 sec)

mysql> update roleType set name = "Person" where name = "Persons";
Query OK, 0 rows affected (0,00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql>

mysql> SHOW VARIABLES LIKE "%VERSION%";
+--------------------------+-------------------------------+
| Variable_name            | Value                         |
+--------------------------+-------------------------------+
| immediate_server_version | 999999                        |
| innodb_version           | 8.0.20                        |
| original_server_version  | 999999                        |
| protocol_version         | 10                            |
| slave_type_conversions   |                               |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version                  | 8.0.20                        |
| version_comment          | MySQL Community Server - GPL  |
| version_compile_machine  | x86_64                        |
| version_compile_os       | Linux                         |
| version_compile_zlib     | 1.2.11                        |
+--------------------------+-------------------------------+
11 rows in set (0,00 sec)

Sorry....there was a mistake in my UPDATE statement in the test case.  Should read:
use test;
create table `test`.`roleType` (
	`name` varchar(25) NOT NULL,
	`baseName` varchar(25) NOT NULL,
	PRIMARY KEY(`name`),
	KEY `baseName` (`baseName`));
create table `test`.`baseRoleType` (
	`name` varchar(25) NOT NULL,
	PRIMARY KEY (`name`),
	CONSTRAINT `baseRoleType.name_roleType.name` FOREIGN KEY (`name`) REFERENCES `test`.`roleType` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE);
alter table `test`.`roleType`
	ADD CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `test`.`baseRoleType` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE;
SET @@foreign_key_checks = FALSE;
insert into `test`.`baseRoleType` (`name`) values ("Person");
SET @@foreign_key_checks = TRUE;
insert into `test`.`RoleType` (`name`, `baseName`) values ("Person","Person");
update `test`.`roleType` set `name` = "Persons" where `name` = "Person";

Fails with:
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`test`.`roletype`, CONSTRAINT `roleType.baseName_baseRoleType.name` FOREIGN KEY (`baseName`) REFERENCES `baseroletype` (`name`) ON DELETE RESTRICT ON UPDATE CASCADE)

Hi Mr. Lanier,

You have a recursive constraint, with the UPDATE that can not work in this situation.

Can you please explain why do you think that it should work ????

First, the field to be updated in the original UPDATE of the first parent table is different than the field in the failing FK Constraint. The original field is `roleType`.`name`.  This causes a Cascade to the child `baseRoleType`.`name`. Then, `baseRoleType` becomes the parent Cascading to the now child `roleType`.`baseName`.

Please refer to the original Description section of this Bug report referencing the MySql 8.0 Reference Manual.  According to the documentation, this should work.  Making the case of why it is needed is easy enough to do, but the point of the Bug report is that the behavior where BOTH tables are Parents AND Children is not in keeping with the documentation.

Hi,

SQL standard clearly specifies that, if you utilise cyclic constraints, the only action that is supported is NO ACTION. No changes in those columns are allowed.

MySQL follows that standard. Hence, what we can do is make this a documentation improvement bug, since this is not mentioned anywhere in our Reference Manual.

Let us know if you agree with this resolution.

I agree.  Please refer to the original Description section of this Bug report referencing the MySql 8.0 Reference Manual....Section 13.1.20.5 FOREIGN KEY Constraints "Referential Actions" under "Cascade" states.... "If a FOREIGN KEY clause is defined on both tables in a foreign key relationship, making both tables a parent and child, an ON UPDATE CASCADE or ON DELETE CASCADE subclause defined for one FOREIGN KEY clause must be defined for the other in order for cascading operations to succeed. If an ON UPDATE CASCADE or ON DELETE CASCADE subclause is only defined for one FOREIGN KEY clause, cascading operations fail with an error."

The way this is written, I was under the impression that this was describing behavior of MySql that was an extension to the standard.  Please address this section in particular, and if this is NOT in error, please make the text more clear as to the subject matter.

Please update Bug status

Thank you Mr. Lanier.

Verified as a documentation bug.

Just to let you know that this documentation bug is still present.

https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#foreign-key-referen...

Thank you.

This notice will be presented to our Documentation team.