Referential integrity constraint violation: when deleting Entity in Hibernate (H2 in memory DBMS)

Question

I am building my first Spring Boot application. I use Hibernate and an H2 in-memory DBMS.

What I am trying to build is a REST API that represents a number of App-Stores. I have an entity called App and another called Store. A store can contain many apps and each app can be contained in more than one store. Apps however, do not know in which stores they are contained. I want to be able to delete apps and stores independently of each other. Just because a store was deleted does not mean the apps therein should be deleted too and vice versa. Apps can exist without being in a store and stores without apps are fine too.

Here is the code for my entities, LpApp is the implementation for an App and LpTemplate is the implementation of a Store:

@Entity
public class LpApp {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false, updatable = false)
    private Long id;

    @NotBlank(message = "An app needs a non-empty name")
    @Column(nullable = false, updatable = false, unique = true)
    private String appName;

    // ... constructors, getters, setters, no further annotations
}

@Entity
public class LpTemplate {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false, updatable = false)
    private Long id;

    @ManyToMany(fetch=FetchType.EAGER)
    @JoinTable(name = "template_apps",
        inverseJoinColumns = { @JoinColumn(name = "app_id") },
        joinColumns = { @JoinColumn(name = "template_id") })
    private Set<LpApp> apps = new HashSet<>();

    // ... constructors, getters, setters, no further annotations

}

This works well until I attempt to delete an App or Store from my DBMS. At this point I get an org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException.

The exception I get is the following (I trimmed the call stack for brevity):

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint ["APP_ID: PUBLIC.TEMPLATE_APPS FOREIGN KEY(APP_ID) REFERENCES PUBLIC.LP_APP(ID) (3)"; SQL statement:
delete from lp_app where id=? [23503-199]]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
...
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
...
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Referential integrity constraint violation: "APP_ID: PUBLIC.TEMPLATE_APPS FOREIGN KEY(APP_ID) REFERENCES PUBLIC.LP_APP(ID) (3)"; SQL statement:
delete from lp_app where id=? [23503-199]

I am obviously doing something wrong, but I don't know where to look. I guess I am not using the @ManyToMany annotation right or perhaps it is the wrong annotation for my use case.

Thank you very much.

I tried to use the entities created by you and with cascade ALL I am able to execute delete transaction successfully. Please post the code where you are trying to delete for more clarity. — ialam, Oct 25 '19 at 13:11
I use the void deleteById(ID id) from org.springframework.data.repository.CrudRepository. — Nic.Star, Oct 25 '19 at 13:27

score 1 · Answer 1 · answered Oct 25 '19 at 12:51

1

You need to add cascade attribute to tell hibernate not to delete the entity on delete operation. There are different options for cascade. Refer to this link to understand the different options.

answered Oct 25 '19 at 12:51

Ravik

694
7
12

but what kind of cascade attribute do I need to set? I tried it with REMOVE but I got the same exception. I also tried ALL, didnt go any different. – Nic.Star Oct 25 '19 at 12:52
If your need is just to read from database while the App or Store entity is loaded then you can use NONE as it will ignore all the operations and will not sync the changes to database – Ravik Oct 25 '19 at 13:02
I definitely need to delete Apps and Stores at some point in time. And when that happens the template_apps table in the database which mapps apps to templates needs to be updated correctly. How do I achieve that? – Nic.Star Oct 25 '19 at 13:25

score -1 · Answer 2 · answered Oct 28 '20 at 19:24

-1

The short version of the answer is that you are trying to delete a record that has an existing relationship with another record.

answered Oct 28 '20 at 19:24

Domkat B Stephen

21
6

Referential integrity constraint violation: when deleting Entity in Hibernate (H2 in memory DBMS)

2 Answers2