1

I have a DB with some foreign keys that are configured without ON DELETE CASCADE; I'd like to avoid changing this if possible. Similarly, our JPA relationships are not configured with cascade = CascadeType.REMOVE.

Is it still possible to delete an entity "in cascade mode", i.e. automatically generating all the necessary delete queries for its children?

Ideally I'd want to do it through JPA, but a raw SQL query would work as well. The main problem is that not all (but most) foreign key constraints are reflected with JPA relationships like OneToOne, OneToMany, et cetera.

Luis Cadena
  • 102
  • 1
  • 2
  • 15
Bluefire
  • 13,519
  • 24
  • 74
  • 118
  • 2
    You may use BEFORE DELETE trigger, for example (but it is possible that after slave delete master record delete will fail). You may use multi-table DELETE (the query text will depend on structure changes). You may use stored procedure with dynamic SQL (get info about FK and delete referential records too)... it seems it is the only non-problematic way, but it is complex... – Akina Dec 25 '19 at 18:50
  • Would [a `DescriptorCustomizer`](https://wiki.eclipse.org/EclipseLink/Examples/JPA/SoftDelete) work for you? The simpler solution would be, of course, to simply add the missing relationships and use `CascadeType.REMOVE` everywhere – crizzis Dec 31 '19 at 15:51
  • If there already are relationships on most of the foreign keys, why wouldn't you just put the cascadeType.Remove on them? That is as JPA as it gets and just leaves you with foreign keys that JPA knows nothing about to deal with - to which a more specific question would give you a more specific solution, to which a simple PreRemove event would allow you to handle looking up the entity and forcing its removal (Simple bulk delete JPA query using the fk value might do depending on the level of cascading required) – Chris Jan 03 '20 at 18:59

1 Answers1

0

I don't know JPA so watch out for dollar signs in my code below, they signify variables. So as already suggested, you may be writing your cascades as triggers.

In case you need a booster on some of the logic you'll be using:

Say you have:

  • table_1, the primary entity table
  • table_2, the "many" table in a one-to-many relationship with table_1

... and table_1 has an FKEY to table_2:

delete from table_2 where id in (select table_2_id from table_1 where id = $table_1_id)

Or say the FKEY is on the opposite table:

delete from table_2 where table_1_id = $table_1_id

Or say you have:

  • table_1, the primary entities
  • table_2, an associated entity table with many-to-many relationship to table_1
  • link_table, which records the many-to-many associations by consisting only of two FKEY's and a row id
  • and you only want to delete the associations because of the M2M relation
delete from link_table where table_1_id = $table_1_id

Or perhaps this is some master record and the many-to-many relationships should cascade-delete...

delete from table_2 where id in (select table_2_id from link_table where table_1_id = $table_1_id);
delete from link_table where table_1_id = $table_1_id;
Nathan Hawks
  • 557
  • 4
  • 14