Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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? –  thyandrecardoso Oct 18 '13 at 14:56
    
Yes, you are right, the "same time" is a crucial part of when this occurs. –  ypercube Oct 18 '13 at 15:04

2 Answers 2

up vote 2 down vote accepted

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 |
+---------------------+--------+---------------------+--------+--------+
share|improve this answer
    
This "turn off" of fk checks works only for the current connection? So, is this safe for a web application and concurrent users? –  thyandrecardoso Oct 20 '13 at 13:12
    
... in any case, it is a drawback to have to update all the other tables "manually". –  thyandrecardoso Oct 20 '13 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. –  Bill Karwin Oct 20 '13 at 15:43

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

share|improve this answer
    
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 –  thyandrecardoso Oct 18 '13 at 15:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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