Project

General

Profile

Actions

Feature #84639

closed

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

Added by Stefan P almost 6 years ago. Updated over 2 years ago.

Status:
Closed
Priority:
Should have
Assignee:
-
Category:
DataHandler aka TCEmain
Start date:
2018-04-06
Due date:
% Done:

0%

Estimated time:
PHP Version:
Tags:
Complexity:
Sprint Focus:

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

Actions #1

Updated by Benni Mack almost 6 years ago

  • Target version changed from 8.7.13 to 8.7.19
Actions #2

Updated by Susanne Moog over 5 years ago

  • Target version changed from 8.7.19 to Candidate for patchlevel
Actions #3

Updated by Tymoteusz Motylewski over 5 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

Actions #4

Updated by Stefan P over 5 years ago

Sorry, don't have time for this. Just put an index on any simple 1:n (IRRE) relation to test it.

Actions #5

Updated by Susanne Moog about 4 years ago

  • Tracker changed from Bug to Feature
  • Category set to DataHandler aka TCEmain
Actions #6

Updated by Christian Kuhn over 2 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.

Actions

Also available in: Atom PDF