rlucassen wrote:
So LLBLGEN lets me build relations but does not guarantee integerity.
It never says it does. It simply maps entity types onto db elements. The referential integrity is a matter of the storage system. An ORM is the intermediate layer between elements in memory and elements in the DB, it's not THE db. I have no idea how you ever got that idea.
How would a client side system ever be able to reliably check this? All it can do is query the FK tables (of which there can be several) if an FK row points to the PK row which is removed, but that's unreliable, as it's not a single user / single connection system.
what is the best solution to this problem i don't think i'm the only one using LLBLGEN with partitions ?
Databases other than MySQL solve this with FK constraints. That MySQL requires for partitioned tables that FK constraints are not present is merely a problem with MySQL, a database not known for reliable FKs anyway.
So best advice I can give you is move away from MySQL, which is a good idea regardless of this problem, considering it's not even having the concept of a working atomic transaction on a single node.
Maybe a delete trigger on the PK table could help: it then queries all the known FK tables whether a row exists with the PK about to be removed. This is more reliable than doing it client side as the trigger runs inside the database and less time is lost, but still it can't guarantee anything: if it has to scan 3 FK tables, and when scanning the 2nd some user inserts a row in the first table (already scanned) with an FK value equal to the PK row about to be removed, you're going to have the same problem: an orphaned row.