JOOQ CASCADE Delete

Question

Cascade deletion is not allowed at the database level and should be implemented in the application layer. Trying to implement using JOOQ. At present my thoughts are as follows

Given: a parent record that extends UpdatableRecord

Get the list of foreign keys referring to this parent's primary key using parentRecord.getTable().getPrimaryKey().getReferences().
delete child records where child.parentId = parentId
implement a recursive function to handle multiple levels of parent-child relationship.

Am I on the right track? Is this functionality already present in JOOQ? Thanks for any hint.

*"Cascade deletion is not allowed at the database level and should be implemented in the application layer"* - Why? The database is quite likely to handle this much better and faster... — Lukas Eder, Mar 30 '18 at 18:03
@LukasEder This is our teamlead's decision to prevent any accidental loss if working with DB manually. Only app should be allowed to delete data in tables. — fh76, Mar 31 '18 at 09:37

score 4 · Answer 1 · answered Mar 30 '18 at 18:14

Am I on the right track?

Well, for starters, I really would insist on using database functionality for this. It's quite likely the database will handle this better, and definitely faster than if you roll it manually from the client.

If that's not an option, an alternative might be to write a stored procedure that implements the cascading deletion, in order to prevent the many server round trips that might incur otherwise.

If that's not an option either, then yes, your approach is logically correct, but make sure that you're not going to produce an N+1 problem. The most efficient solution is to recursively go to the leaf child tables first, delete all the relevant rows there in a single bulk delete (semi-joining the entire path up to the original table's deleted rows) and then recurse up the tree. For example:

If you want to emulate a statement like (hypothetical syntax):

DELETE CASCADE FROM a WHERE a_id IN (1, 2, 3)

Then, you should run:

DELETE FROM e WHERE c_id IN (
  SELECT c_id FROM c WHERE a_id IN (1, 2, 3)
);
DELETE FROM e WHERE c_id IN (
  SELECT c_id FROM c WHERE a_id IN (1, 2, 3)
);
DELETE FROM c WHERE a_id IN (1, 2, 3);
DELETE FROM a WHERE a_id IN (1, 2, 3);

jOOQ will definitely help you generate these dynamically.

Is this functionality already present in JOOQ?

No, but it would be a nice addition: https://github.com/jOOQ/jOOQ/issues/7367

JOOQ CASCADE Delete

1 Answers1