Foreign keys reference two child tables to parent table & grandchild table to the two child tables, updating parent table foreign key constraint fails

Question

I have created 4 tables - p_table, c_table, c2_table and cc_table. The table structures are as follows :-

SHOW CREATE TABLE p_table;
| p_table | CREATE TABLE `p_table` (
  `p_table_id` int unsigned NOT NULL AUTO_INCREMENT,
  `value` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`p_table_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5556 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

SHOW CREATE TABLE c_table;
| c_table | CREATE TABLE `c_table` (
  `c_table_id` int unsigned NOT NULL AUTO_INCREMENT,
  `p_table_id` int unsigned DEFAULT NULL,
  `value` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`c_table_id`),
  KEY `i_c_table` (`p_table_id`),
  CONSTRAINT `fk_p_table` FOREIGN KEY (`p_table_id`) REFERENCES `p_table` (`p_table_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=556 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

SHOW CREATE TABLE c2_table;
| c2_table | CREATE TABLE `c2_table` (
  `c2_table_id` int unsigned NOT NULL AUTO_INCREMENT,
  `p_table_id` int unsigned DEFAULT NULL,
  `value` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`c2_table_id`),
  KEY `i_c2_table` (`p_table_id`),
  CONSTRAINT `fk2_p_table` FOREIGN KEY (`p_table_id`) REFERENCES `p_table` (`p_table_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

SHOW CREATE TABLE cc_table;
| cc_table | CREATE TABLE `cc_table` (
  `cc_table_id` int unsigned NOT NULL AUTO_INCREMENT,
  `p_table_id` int unsigned DEFAULT NULL,
  `value` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`cc_table_id`),
  KEY `i_cc_table` (`p_table_id`),
  CONSTRAINT `fk_c2_table` FOREIGN KEY (`p_table_id`) REFERENCES `c2_table` (`p_table_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_c_table` FOREIGN KEY (`p_table_id`) REFERENCES `c_table` (`p_table_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

Here, column p_table_id of table c_table and column p_table_id of table c2_table references to column p_table_id of table p_table via foreign key.

Again, column p_table_id of table cc_table references to column p_table_id of table c_table and column p_table_id of table c2_table via foreign key.

The reference option in all the references is -

ON DELETE RESTRICT ON UPDATE CASCADE

It is some sort of 1-2-1 chain formation.

Now, I populate the 4 tables as follows :-

SELECT * FROM p_table;
+------------+----------+
| p_table_id | value    |
+------------+----------+
|          1 | p_value1 |
|          2 | p_value2 |
|          3 | p_value3 |
|          4 | p_value4 |
|          5 | p_value5 |
+------------+----------+

SELECT * FROM c_table;
+------------+------------+-----------+
| c_table_id | p_table_id | value     |
+------------+------------+-----------+
|          1 |          1 | c_value11 |
|          2 |          2 | c_value21 |
|          3 |          2 | c_value22 |
|          4 |          3 | c_value31 |
|          5 |          3 | c_value32 |
|          6 |          3 | c_value33 |
|          7 |          4 | c_value41 |
|          8 |          4 | c_value42 |
|          9 |          4 | c_value43 |
|         10 |          4 | c_value44 |
|         11 |          5 | c_value51 |
|         12 |          5 | c_value52 |
|         13 |          5 | c_value53 |
|         14 |          5 | c_value54 |
|         15 |          5 | c_value55 |
+------------+------------+-----------+

SELECT * FROM c2_table;
+-------------+------------+-----------+
| c2_table_id | p_table_id | value     |
+-------------+------------+-----------+
|           1 |          1 | c_value11 |
|           2 |          2 | c_value21 |
|           3 |          2 | c_value22 |
|           4 |          3 | c_value31 |
|           5 |          3 | c_value32 |
|           6 |          3 | c_value33 |
|           7 |          4 | c_value41 |
|           8 |          4 | c_value42 |
|           9 |          4 | c_value43 |
|          10 |          4 | c_value44 |
|          11 |          5 | c_value51 |
|          12 |          5 | c_value52 |
|          13 |          5 | c_value53 |
|          14 |          5 | c_value54 |
|          15 |          5 | c_value55 |
+-------------+------------+-----------+

SELECT * FROM cc_table;
+-------------+------------+------------+
| cc_table_id | p_table_id | value      |
+-------------+------------+------------+
|           1 |          1 | cc_value11 |
|           2 |          1 | cc_value12 |
|           3 |          1 | cc_value13 |
|           4 |          1 | cc_value14 |
|           5 |          1 | cc_value15 |
|           6 |          2 | cc_value21 |
|           7 |          2 | cc_value22 |
|           8 |          2 | cc_value23 |
|           9 |          2 | cc_value24 |
|          10 |          3 | cc_value31 |
|          11 |          3 | cc_value32 |
|          12 |          3 | cc_value33 |
|          13 |          4 | cc_value41 |
|          14 |          4 | cc_value42 |
|          15 |          5 | cc_value51 |
+-------------+------------+------------+

Now, I try to update a value in table p_table. The expected behavior is that the corresponding values of all the child tables will get updated.

But, I get the following error -

UPDATE p_table
    -> SET p_table_id = 3333 WHERE p_table_id = 3;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`constraints`.`cc_table`, CONSTRAINT `fk_c_table` FOREIGN KEY (`p_table_id`) REFERENCES `c_table` (`p_table_id`) ON DELETE RESTRICT ON UPDATE CASCADE)

Now, I try removing the foreign key fk_c2_table linking cc_table and c2_table. Still all the child tables are ultimately linked to parent table p_table.

ALTER TABLE cc_table
    -> DROP FOREIGN KEY fk_c2_table;

Now, I try to update a value in table p_table, and I succeed.

UPDATE p_table
    -> SET p_table_id = 3333 WHERE p_table_id = 3;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

The corresponding values in the three child tables - c_table, c2_table and c2_table also gets updated as expected.

Thus the gist from here is that a chain formation of type - 1-1-1 works but a chain formation of type - 1-2-1 does not work. Why is it so? Is there any way that I can make it work?

On further investigation, if linking a child table to two parent tables work or not, I remove the foreign keys - fk_p_table, fk2_p_table.

ALTER TABLE c_table
    -> DROP FOREIGN KEY fk_p_table;

ALTER TABLE c2_table 
    -> DROP FOREIGN KEY fk2_p_table;

Also I re-add the foreign key - fk_c2_table.

ALTER TABLE cc_table
    -> ADD CONSTRAINT fk_c2_table FOREIGN KEY (p_table_id)
    -> REFERENCES c2_table (p_table_id)
    -> ON DELETE RESTRICT
    -> ON UPDATE CASCADE;

The table structures of c_table, c2_table and cc_table are now as follows :-

SHOW CREATE TABLE c_table;
| c_table | CREATE TABLE `c_table` (
  `c_table_id` int unsigned NOT NULL AUTO_INCREMENT,
  `p_table_id` int unsigned DEFAULT NULL,
  `value` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`c_table_id`),
  KEY `i_c_table` (`p_table_id`)
) ENGINE=InnoDB AUTO_INCREMENT=556 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

SHOW CREATE TABLE c2_table;
| c2_table | CREATE TABLE `c2_table` (
  `c2_table_id` int unsigned NOT NULL AUTO_INCREMENT,
  `p_table_id` int unsigned DEFAULT NULL,
  `value` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`c2_table_id`),
  KEY `i_c2_table` (`p_table_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

SHOW CREATE TABLE cc_table;
| cc_table | CREATE TABLE `cc_table` (
  `cc_table_id` int unsigned NOT NULL AUTO_INCREMENT,
  `p_table_id` int unsigned DEFAULT NULL,
  `value` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`cc_table_id`),
  KEY `i_cc_table` (`p_table_id`),
  CONSTRAINT `fk_c2_table` FOREIGN KEY (`p_table_id`) REFERENCES `c2_table` (`p_table_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_c_table` FOREIGN KEY (`p_table_id`) REFERENCES `c_table` (`p_table_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

So, now this is basically a 1-2 chain formation.

Now, I try updating a value in table c_table and table c2_table and I succeed -

UPDATE c_table
    -> SET p_table_id = 2 WHERE p_table_id = 1;

UPDATE c2_table
    -> SET p_table_id = 4 WHERE p_table_id = 5;

The corresponding values in the child table cc_table also gets updated as expected.

So, again the gist is that both 2-1 and 1-2 chain formations work, but 1-2-1 chain formation does not work.

Why a 1-2-1 chain formation is not working? Is there any way that I can make this work?

I rarely see the need for updating FKs. Can you elaborate on why do you need to do it? — The Impaler, Sep 17 '21 at 13:40
I am learning MySQL and trying out every possible scenario that can arise by putting every thing into rigorous testing (a sort of bug hunting). As of now I have no practical projects to apply my work on. — Payel Senapati, Sep 17 '21 at 13:46
I tested the most simple case in MySQL 8.x and it worked well. See https://www.db-fiddle.com/f/cFK7AvDyFFx2pnAqeppQDC/0 What version of MySQL are you using? — The Impaler, Sep 17 '21 at 13:57
Well your case is overtly simple and I assume that you have not gone through my entire question. That works for me as well. Here my case is 1 parent to two child to a common grand child. It does not work only on this particular case. In all other cases it works. — Payel Senapati, Sep 17 '21 at 14:26

score 1 · Accepted Answer · answered Sep 17 '21 at 18:40

Good catch!

This seems to be a bug in MySQL 8.x. I tried a simplified example with all 4 FKs as you specified and I was able to reproduce the error. I would file a bug to the MySQL team. It's not that big of a thing since PKs/FKs rarely change (though they can), and also your example is a big of a stretch (though it should work).

I tried the same script in PostgreSQL 13 and it works like a charm. See below.

MySQL 8 Example -- Does Not Work

See DB Fiddle - MySQL 8:

create table t (a int primary key not null);

create table u (
  a int primary key not null,
  constraint fk1 foreign key (a) references t (a) on update cascade
);

create table v (
  a int primary key not null,
  constraint fk2 foreign key (a) references t (a) on update cascade
);

create table w (
  a int,
  constraint fk3 foreign key (a) references u (a) on update cascade,
  constraint fk4 foreign key (a) references v (a) on update cascade
);

insert into t (a) values (123);
insert into t (a) values (456);
insert into u (a) values (123);
insert into u (a) values (456);
insert into v (a) values (123);
insert into w (a) values (123);

update t set a = 789 where a = 123;

Produces the error:

ER_NO_REFERENCED_ROW_2: Cannot add or update a child row: a foreign key constraint fails (test.w, CONSTRAINT fk4 FOREIGN KEY (a) REFERENCES v (a) ON UPDATE CASCADE)

PostgreSQL 13 Example -- Works

See DB Fiddle - PostgreSQL 13:

create table t (a int primary key not null);

create table u (
  a int primary key not null,
  constraint fk1 foreign key (a) references t (a) on update cascade
);

create table v (
  a int primary key not null,
  constraint fk2 foreign key (a) references t (a) on update cascade
);

create table w (
  a int,
  constraint fk3 foreign key (a) references u (a) on update cascade,
  constraint fk4 foreign key (a) references v (a) on update cascade
);

insert into t (a) values (123);
insert into t (a) values (456);
insert into u (a) values (123);
insert into u (a) values (456);
insert into v (a) values (123);
insert into w (a) values (123);

update t set a = 789 where a = 123;

All four tables are updated as expected, as you can see in the fiddle.

Thank you for the answer. Also I think I may have found another bug in MySQL. Please check out my question and the answer provided by me - https://stackoverflow.com/questions/69105421/tilde-operator-in-boolean-full-text-search-in-mysql-is-not-behaving-as-sta/69121363#69121363 — Payel Senapati, Sep 18 '21 at 06:56

Foreign keys reference two child tables to parent table & grandchild table to the two child tables, updating parent table foreign key constraint fails

1 Answers1

MySQL 8 Example -- Does Not Work

PostgreSQL 13 Example -- Works