Java Spring cascade elementcollection delete

Question

For some reason my delete is not cascading when I try to delete the parent element which has an elementcollection in it, the two classes are as follows:

@Entity
@Table(name="Timestamps")
@JsonIgnoreProperties(ignoreUnknown = true)
public class ProductList {
    private boolean success;
    @Id
    private Date lastUpdated;

    private String time = "minute";

    @ElementCollection
    @MapKeyColumn(name="product_id")
    @CollectionTable(name="Products")
    private Map<String,Product> products;

And:

@Embeddable
@JsonIgnoreProperties(ignoreUnknown = true)
public class Product{
    @Embedded
    private Status quick_status;

Currently this is the only field that I have in the class because I have removed all the others trying to figure out why when I try to delete a parent object the delete does not cascade to the Products table. Below is the query I am running:

DELETE FROM Timestamps list WHERE list.last_updated !=0;

The last_updated value will always be non-zero, so I am just using this query to test deleting, but even when I run the query in the mysql shell I get "Cannot delete or update a parent row: a foreign key constraint fails" I thought that the elementcollection annotation was suppose to automatically cascade, is there something that I am missing?

EDIT, when Below are the sql commands that Hibernate is sending, as you will notice on the third one it is missing the cascade.

Hibernate: create table products (product_list_last_updated datetime(6) not null, buy_price float not null, sell_price float not null, product_id varchar(255) not null, primary key (product_list_last_updated, product_id)) engine=InnoDB
Hibernate: create table timestamps (last_updated datetime(6) not null, success bit not null, time varchar(255), primary key (last_updated)) engine=InnoDB
Hibernate: alter table products add constraint FKo31ur4gpvx1d5720rgs3qaawi foreign key (product_list_last_updated) references timestamps (last_updated)

EDIT 2: Below is the @Query that I have for the ProductListRepository class, I included on the query that is relevant for deleting.

@Repository
public interface ProductListRepository extends CrudRepository<ProductList, Integer>{
    @Modifying
    @Query(value = "DELETE FROM Timestamps list WHERE list.last_updated !=0", nativeQuery = true)
    void deleteOld();
}

Answer 1

There are several variables in play. @ElementCollection has some limitations. See: https://en.wikibooks.org/wiki/Java_Persistence/ElementCollection

The limitations of using an ElementCollection instead of a OneToMany is that the target objects cannot be queried, persisted, merged independently of their parent object. They are strictly privately-owned (dependent) objects, the same as an Embedded mapping. There is no cascade option on an ElementCollection, the target objects are always persisted, merged, removed with their parent. ElementCollection still can use a fetch type and defaults to LAZY the same as other collection mappings.

It works as intended because productRepository.deleteAll() works.

Why doesn't work with a native query? Because native queries are executed "as is", meaning that it won't account the annotations in the entities.

Additionnaly, because it is annotated with @ElementCollection it didn't include any ON DELETE CASCADE in your constraint foreign key

Aka, this alter table... does not have ON DELETE CASCADE

alter table products add constraint FKo31ur4gpvx1d5720rgs3qaawi foreign key (product_list_last_updated) references timestamps (last_updated)

Suggested Solition 1

Change from @ElementCollection to @OneToMany with the intended cascade options.

Suggested Solition 2

Remove the nativeQuery = true and use a JPA query instead. It should look something like:

@Modifying
@Query("DELETE FROM ProductList list WHERE list.lastUpdated != 0")
void deleteOld();

Suggested Solition 3

Use Spring Data named queries in your ProductListRepository. Something like:

deleteByLastUpdatedNot(Date date);
or 
deleteByLastUpdatedLessThan(Date date);

Answer 2

(Caveat: I do not know what was in the designers' minds.)

To me, automatic deletion of data is scary. A slight misunderstanding on my part could lead to data being deleted that I did not expect.

Sure, in a well-designed, textbook-style, schema, the data will be perfectly structured and it should be obvious what to delete and what not to delete.

But textbook schemas are limited to textbooks.

Answer 3

In mysql shell you aren't able to execute the query because you didn't set your foreign key ON DELETE CASCADE in your mysql. Adding ON DELETE CASCADE to your foreign key will allow mysql shell to cascade on delete.

It seems illogical to delete product once it's logs been deleted.

I think the structure must look something like this:

• CREATE TABLE products(id INT NOT NULL AUTO_INCREMENT, buy_price FLOAT, sell_price FLOAT, lastUpdateDate DATETIME, PRIMARY KEY (id));
• CREATE TABLE timestamps(id INT NOT NULL AUTO_INCREMENT, product INT NOT NULL, success BIT NOT NULL,updateDate DATETIME NOT NULL,PRIMARY KEY (id), FOREIGN KEY (product) REFERENCES products(id) ON DELETE CASCADE);

So now when ever you delete a product it's logs gonna be deleted.