Pair of 2-tuple composite FKs pointing to same table (closure table pattern)

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

• Table father has a composite PK (col1, col2)

• 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?

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

StackExchange Database Administrators Q#51764, answer score: 2