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.

Solution

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.

Comments

[isADon]

I need to use a JpaRepository to use flush(), right? How would I use it with a PagingAndSortingRepository for example?

[Samer Adra]

@isADon JpaRepository is a PagingAndSortingRepository. Its signature is: public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T>