Propagate cascade delete raises foreign key constraint fails

Question

I'm using Spring Boot (2.1.0.RELEASE) with Spring Data JPA. Database is MySQL.

I have some problem to chain cascade delete.

I have the following model :

I do not use @ManyToMany because I need additional fields in generated tables, so my entities are as follow (useless properties have been removed) :

@Audited
@Entity
@Table(name = "request")
public class Request {

    @OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
    private Resource resource;

}


@Audited
@Entity
@Table(name = "resource")
public class Resource {

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "resource")
    private Set<ResourceArticle> resourceArticles;

}

@Audited
@Entity
@Table(name = "resource_article")
public class ResourceArticle {

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "article_id")
    private Article article;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "resource_id")
    private Resource resource;

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "resourceArticle")
    private Set<ResourceArticleOption> options;

}

@Audited
@Entity
@Table(name = "resource_article_option")
public class ResourceArticleOption {

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "option_id")
    private Option option;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "resource_article_id")
    private ResourceArticle resourceArticle;

}

Then from my manager I simply issue the request delete extending CrudRepository :

/* Repositroy */
public interface RequestRepository extends CrudRepository<Request, Long> {
}

/* Manager */
@Transactional
@Component("requestMgr")
public class RequestManager {

    @Autowired
    RequestRepository requestRepository;

    public void delete(Request request) {
        requestRepository.delete(request);
    }

}

/* Viewmodel */
public class RequestVm {

    @WireVariable
    private RequestManager requestMgr;

    public void deleteRequest(Request req) {
        requestMgr.delete(req);
    }

}

Error is :

Caused by: java.sql.SQLException: Cannot delete or update a parent row: a foreign key constraint fails (my_db.resource_article, CONSTRAINT FK5wqvprkwx05fb5hgt6w9h7nbk FOREIGN KEY (resource_id) REFERENCES resource (id))

The output when enabling tracing :

delete from request where id=?
binding parameter [1] as [BIGINT] - [24]
delete from resource where id=?
binding parameter [1] as [BIGINT] - [71]
SQL Error: 1451, SQLState: 23000
(conn=30) Cannot delete or update a parent row: a foreign key constraint fails (`my_db`.`resource_article`, CONSTRAINT `FK5wqvprkwx05fb5hgt6w9h7nbk` FOREIGN KEY (`resource_id`) REFERENCES `resource` (`id`))

Strange that it tries to delete in the order request > resource > resource_article > resource_article_option ?

I need CascadeType.ALL as I want to PERSIST and DELETE.

I can break the chain by setting reference to null before deleting but of course it results in orphan records in the database.

What is the best strategy here ?

Note that you can cascade multiple operations without cascading *all* operations. That doesn't necessarily mean that you don't need or want `CascadeType.ALL`, or that using it is causing your problem, just that the reason you give for using it is not valid (JPA recognizes more operations than just `PERSIST` and `REMOVE`). — John Bollinger, May 29 '19 at 15:55
As far as I know or can tell, deleting a `Resource` *via* a repository extending `CrudRepository` should honor the JPA cascading properties declared for the parent entity. That that does not appear to be happening leads me to suspect that something is not as you present it, or else that something important has been omitted. Perhaps the deployed versions of your entities do not have cascading enabled? Perhaps you've customized your repository such that its `delete()` method somehow bypasses JPA? — John Bollinger, May 29 '19 at 16:40
Thanks John. Indeed I misspoke, I know I can use multiple cascade types, but as I understand I have no reason not to cascade everything so I use `ALL`. Other properties I have not presented here are basic `@Column` and should not cause any problem. The only thing that I have voluntary omitted is that I have another table on top of this, a `REQUEST` table which have a 1-1 relation to `RESOURCE`, the delete is done on this request. Finally note that I use Envers for auditing, my entities have `@Audited` annotation, so for each table I got another `xxx_audit_log` table. I have updated my post. — Yann39, May 31 '19 at 08:57

score 0 · Accepted Answer · answered Jun 11 '19 at 09:21

I got it to work by removing in each relationship the entity that is not the owner of the relationship.

Cascade remove in @ManyToMany relationships is not only applied to the link table but to the other side of the relationship as well.

It is a well-known problem when deleting from @ManyToMany associations (this is well explained here for example), but I did not pay attention because my annotations are not direct @ManyToMany but double @OneToMany associations, and I though JPA would cascade delete like in simple @OneToMany relationships but it seems I was wrong.

I have used @preRemove to clean the relation before deleting the request :

In ResourceArticle :

@PreRemove
public void preRemove() {
    article.getResourceArticles().remove(this);
}

In ResourceArticleOption :

@PreRemove
public void preRemove() {
    option.getResourceArticleOptions().remove(this);
}

Then all is working perfectly.

Propagate cascade delete raises foreign key constraint fails

1 Answers1