Defining a FOREIGN KEY constraint on a 1:n relation ends in an SQL failure when editing rows in these tables via backend or extbase
We have a lot of 1:n relation configured as TCA type "inline" where the uid of the parent is stored in a field in the child table. That works fine. Since we are using MySQL with InnoDB only, we wanted to actually define foreign key constraints on these fields (instead of a pure index). Adding this to the child table
FOREIGN KEY (parent) REFERENCES parent_table (uid) ON DELETE CASCADE
to ext_tables.sql works in the install tool (it correctly adds the key and the constraint - as long as the initial data integrity is given, of course). But as soon as this constraint is present, it is not possible to edit the parent datasets anymore (neither in backend or Extbase persistence) - it always fails with a MySQL exception saying that the foreign key constraint fails on this constraint.
Working with pure SQL on these tables works fine (inserting and deleting correctly referenced rows is working, also the cascading is working). So I suspect that there's some kind of "race condition" in the DataHandler or some other persistence layer that is doing some inserts/deletes/updates in the wrong order (or wrongly doing multiple querys at all, that should actually be a single query in an ideal world), preventing this from working.
Side note: having foreign key constraints on
*_mm tables works perfectly fine in both, backend and extbase, contexts.
So, I'm not sure which category is correct here (DataHandler, Doctrine, Extbase?)