Hello,

because we want to partition our big data table we have to say goodbye to ForeignKeyConstraints The relations can be created by hand no problem.

But the constraints don't work anymore.

i can now delete a parent row making the childrows orphans. we used to get a foreignkeyconstraint exception.

How can we protect these relations

Thanks

rlucassen wrote:

Hello,

because we want to partition our big data table we have to say goodbye to ForeignKeyConstraints The relations can be created by hand no problem.

But the constraints don't work anymore.

i can now delete a parent row making the childrows orphans. we used to get a foreignkeyconstraint exception.

How can we protect these relations

I am not sure what we can do about this? You want the ORM to do foreignkey constraint checks, something the RDBMS doesn't support?

So LLBLGEN lets me build relations but does not guarantee integerity.

what is the best solution to this problem i don't think i'm the only one using LLBLGEN with partitions ?

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.

Moving away from MySQL is not an option right now.

I Noticed the D can be removed from allowed actions so no ORM delete can be performed. Only issue i have is when someone is fiddling in the database itself.

This is where we can add the delete triggers.

Thanks for clarifying and the advice

Otis,

See attached why i got the idea the ORM keeps integrity.

Regard remon

Ah That's for rule propagation for forward mapping, so you define a relationship in the entity model and these rules are applied to an FK if created.

I understand why this might look ambiguous, though, but an ORM can't guarantee integrity in any way, as I've described above with the trigger which will be a challenge.