deleteAll() in Repository randomly causes ConstraintViolationException

Question

I have tests that do CRUD operations on an API. Before each test the test data in the API gets recreated. Meaning deleting all data in the database and inserting the test data again.

public void initDatabase() {
    answerTranslationRepository.deleteAll();
    answerRepository.deleteAll();
    userRepository.deleteAll();
    //....

    Answer answer = new Answer();
    AnswerTranslation answerTranslation = new AnswerTranslation("test", answer);
    //....

    answerTranslationRepository.save(answerTranslation);
    answerRepository.save(answer);  
}

Running all tests works most of the time but every now and then the call answerRepository.deleteAll(); fails with:

2018-04-01 09:09:49.069 ERROR 14260 --- [           main] o.h.i.ExceptionMapperStandardImpl        : HHH000346: Error during managed flush [org.hibernate.exception.ConstraintViolationException: could not execute statement]

org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [fkco3o4hxryohduthxj2vgnuhxs]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement

    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)
    //..... 
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:536)
    ... 54 more
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "answer" violates foreign key constraint "fkco3o4hxryohduthxj2vgnuhxs" on table "answer_translation"
  Detail: Key (id)=(ab54d53a-cd55-428a-aac7-40b20ead86de) is still referenced from table "answer_translation".

Answer has following relation to AnswerTranslation:

@OneToMany(mappedBy = "answer", cascade = CascadeType.ALL)
private List<AnswerTranslation> translations = new ArrayList<>();

AnswerTranslation to Answer:

@ManyToOne
private Answer answer;

An AnswerTranslation can not exist without an Answer.

I can not see why answerRepository.deleteAll(); sometimes fails with the shown error as the method should delete the data from the answerTranslationRepository first before trying to delete the answers.

If your repository extends `JpaRepository`, you can issue `deleteAllInBatch` instead of `deleteAll`. If you only extend `Repository` (or `CrudRepository`), the order of deletes cannot be guaranteed. This is because `deleteAll` is implemented by Spring Data JPA as `findAll().forEach(this::delete)`, meaning, all entities of the given type are loaded and deleted one-by-one. However, JPA spec does not require `delete` to sync changes with the database instantly, which means that all changes (including deletes) are written to the database only when required. — manish, Apr 03 '18 at 11:33

score 6 · Answer 1 · answered Apr 01 '18 at 07:54

This is a common problem while you are working with JPA Hibernate. The database query execution is not translated sequentially from the code. You just have to call flush() after you delete entries from database tables.

So the modified function should look like this.

public void initDatabase() {
    answerTranslationRepository.deleteAll();
    answerTranslationRepository.flush();

    answerRepository.deleteAll();
    answerRepository.flush();

    userRepository.deleteAll();
    userRepository.flush();
    //....

    Answer answer = new Answer();
    AnswerTranslation answerTranslation = new AnswerTranslation("test", answer);
    //....

    answerTranslationRepository.save(answerTranslation);
    answerRepository.save(answer);  
}

You can have a look here in this answer which has a nice explanation.

I need to use a JpaRepository to use flush(), right? How would I use it with a PagingAndSortingRepository for example? — isADon, Apr 01 '18 at 08:57
@isADon JpaRepository is a PagingAndSortingRepository. Its signature is: `public interface JpaRepository extends PagingAndSortingRepository, QueryByExampleExecutor` — Samer Adra, Jul 27 '20 at 16:23

deleteAll() in Repository randomly causes ConstraintViolationException

1 Answers1