Feature #84639
closedDefining a FOREIGN KEY constraint on a 1:n relation ends in an SQL failure when editing rows in these tables via backend or extbase
0%
Description
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 tableFOREIGN 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 uid_local
and uid_foreign
in *_mm
tables works perfectly fine in both, backend and extbase, contexts.
So, I'm not sure which category is correct here (DataHandler, Doctrine, Extbase?)
Updated by Benni Mack over 6 years ago
- Target version changed from 8.7.13 to 8.7.19
Updated by Susanne Moog about 6 years ago
- Target version changed from 8.7.19 to Candidate for patchlevel
Updated by Tymoteusz Motylewski about 6 years ago
Thanks for the report @Stefan.
Can you please provide a minimal code allowing to reproduce the issue with vanilla core?
e.g. please attach a test extension and steps to reproduce.
Thanks
Updated by S P about 6 years ago
Sorry, don't have time for this. Just put an index on any simple 1:n (IRRE) relation to test it.
Updated by Susanne Moog over 4 years ago
- Tracker changed from Bug to Feature
- Category set to DataHandler aka TCEmain
Updated by Christian Kuhn almost 3 years ago
- Status changed from New to Closed
Hey.
I'm going to close the issue for now: The core has no support for foreign key constraints whatsoever in its current state. Adding this probably requires quite a bit of work, a lot of dedicated time and many patches. It might become feasible when the DataHandler is refactored and simplified enough to give an overview on what needs to be done.
This issue here won't help much for now in this regard. In case we pick this up again, we'll created new tickets that include dedicated tasks.
I hope that's fine with everyone for now.