0

I have a named native query which looks so:

@NamedNativeQuery(name = "deleteRecipes", query = "DELETE FROM RECIPE WHERE rcp_acc_identifier IN (?1)")

These are the important parts of the recipe table:

                                              Table "public.recipe"
         Column          |            Type             |                        Modifiers                        
-------------------------+-----------------------------+---------------------------------------------------------
 rcp_pf_id_photodata     | bigint                      | 
 rcp_pf_id_thumbnaildata | bigint                      | 
Indexes:
    "fk_rcp_pf_id_photodata_idx" btree (rcp_pf_id_photodata)
    "fk_rcp_pf_id_thumbnaildata_idx" btree (rcp_pf_id_thumbnaildata)
Foreign-key constraints:
    "fk_rcp_pf_id_photodata" FOREIGN KEY (rcp_pf_id_photodata) REFERENCES persistable_file(pf_id) ON DELETE CASCADE
    "fk_rcp_pf_id_thumbnaildata" FOREIGN KEY (rcp_pf_id_thumbnaildata) REFERENCES persistable_file(pf_id) ON DELETE CASCADE

When I execute the query above, the recipes will be deleted but not the data from persistable_file. Why is this so? Additional information: I didn't set the ON DELETE CASCADE while creating the database. I added it later by dropping both constraints from above at first and then I added the constraints again with the additional ON DELETE CASCADE directive. Is this maybe the problem why the data is not deleted?

Bevor
  • 8,396
  • 15
  • 77
  • 141
  • The problem is that you don't understand how `ON DELETE CASCADE` works. Marking the `rcp_pf_id_photodata` as a foreign key with `ON DELETE CASCADE` means that this record will be deleted when the _parent_ table containing the foreign key value gets deleted, _not_ when then recipe record gets deleted. You might have to restructure your table design here. – Tim Biegeleisen Dec 31 '18 at 13:59
  • @TimBiegeleisen I understand, thanks. Post it as answer, so I can accept it – Bevor Dec 31 '18 at 14:02

0 Answers0