3

I don't have much experience with DB design, and having a hard time figuring out what's wrong with the following:

  1. Table father has a composite PK (col1, col2)
  2. Table closure has ancestor(a-col1, a-col2), descendant(d-col1, d-col2) and length. Ancestor and Descendant are composite FKs, each pointing to the PK of father. The 4 columns (a-col1, a-col2,d-col1, d-col2) are a composite PK.

Look at the following fiddle: http://sqlfiddle.com/#!2/71ad1/1

I am working with MySQL.

I have constraints in CASCADE, but when I try to update the father table, the constraints give me errors. I can't do the following update to father table row:

UPDATE father 
SET `col1` = "UPDATE_MY_ID_PLEASE" 
WHERE `col1` = 'A' AND `col2` = 1;

Is the design wrong? How should this be done?

2
  • I think this shows that the problem occurs when the two constraints are "activated" at the same time: sqlfiddle.com/#!2/0b39f/1 But I don't understand why. What's the problem? Does this work on other DBs? Oct 18, 2013 at 14:56
  • Yes, you are right, the "same time" is a crucial part of when this occurs. Oct 18, 2013 at 15:04

2 Answers 2

2

This is a known limitation of InnoDB. It was reported as a bug way back in 2005:
Bug #11305 On Update Cascade not working with two references to the same field

The bug was acknowledged but not fixed. The following was added to the manual section http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html:

Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

Based on a workaround suggested in the bug, I got the update to work:

mysql> SET SESSION foreign_key_checks=0;

mysql> UPDATE father  SET `col1` = 'UPDATE_MY_ID_PLEASE'  WHERE `col1` = 'A' AND `col2` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE closure SET `a-col1`='UPDATE_MY_ID_PLEASE' WHERE `a-col1` = 'A' AND `a-col2` = 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> UPDATE closure SET `d-col1`='UPDATE_MY_ID_PLEASE' WHERE `d-col1` = 'A' AND `d-col2` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM father ; SELECT * FROM closure;
+---------------------+------+------+
| col1                | col2 | col3 |
+---------------------+------+------+
| A                   |    2 | A2   |
| B                   |    1 | B1   |
| B                   |    2 | B2   |
| UPDATE_MY_ID_PLEASE |    1 | A1   |
+---------------------+------+------+
4 rows in set (0.00 sec)

+---------------------+--------+---------------------+--------+--------+
| a-col1              | a-col2 | d-col1              | d-col2 | length |
+---------------------+--------+---------------------+--------+--------+
| A                   |      2 | A                   |      2 |      0 |
| A                   |      2 | B                   |      2 |      1 |
| UPDATE_MY_ID_PLEASE |      1 | B                   |      1 |      1 |
| UPDATE_MY_ID_PLEASE |      1 | UPDATE_MY_ID_PLEASE |      1 |      0 |
+---------------------+--------+---------------------+--------+--------+
3
  • This "turn off" of fk checks works only for the current connection? So, is this safe for a web application and concurrent users? Oct 20, 2013 at 13:12
  • ... in any case, it is a drawback to have to update all the other tables "manually". Oct 20, 2013 at 13:17
  • SET SESSION ... statements affect only the current connection. Note also that restoring foreign key checks does not scan the data inserted while the check was disabled to ensure it is valid. Oct 20, 2013 at 15:43
2

I don't think this will work in MySQL. It has to do with how MySQL checks constraints (after each and every row update) and not at the end of statement or at the end of transaction. In combination with the double cascade path from father to closure, make this impossible in MySQL with Referential Integrity constraints alone (again, I think it is, if someone posts a solution, I'll be happy to remove my answer.)

A solution in MySQL will probably include some procedure that handles the cascaded updates and deletes itself and with great care, so the constraints hold during every baby step (i.e. every row update.)

In Postgres, it just works though: SQL-Fiddle

1
  • Damn, it seems obvious now... and it's the second time I hit a similar problem (Mysql also does not implement deferred constraints, which come in very handy within transactions)... I'm starting to miss Postgre Oct 18, 2013 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.