How to prevent referential integrity constraint violation in tests?

Question

I use JUnit 5 to create unit tests for my Spring Boot application.

For each testing class, I use @Sql to load the context:

HierarchyEntityServiceTest.java

@SpringBootTest
@ActiveProfiles("test")
@Sql({
  "/sql/import_hierarchy_entity_type.sql",
  "/sql/import_hierarchy_entity.sql",
  "/sql/import_site_department.sql"
})
@Transactional
public class HierarchyEntityServiceTest {

  @Autowired
  private HierarchyEntityService hierarchyEntityService;

  @Test
  void findAllSkillCenters() {
    assertThat(hierarchyEntityService.findAllSkillCenters())
      .hasSize(5);
  }

  @Test
  void findDepartmentsBySiteAndSkillCenter() {
    assertThat(hierarchyEntityService.findDepartmentsBySitesAndSkillCenter(
      List.of(11L),
      "DPT_SWFC1"
    )).hasSize(1);
  }
}

SourceDetailsRepositoryTest.java

@SpringBootTest
@ActiveProfiles("test")
@Sql({
  "/sql/import_hierarchy_entity_type.sql",
  "/sql/import_hierarchy_entity.sql",
  "/sql/import_source_details.sql",
  "/sql/import_candidate.sql"
})
@Transactional
class SourceDetailsRepositoryTest {

  @Autowired
  private CandidateRepository candidateRepository;
  @Autowired
  private SourceDetailsRepository sourceDetailsRepository;

  @Test
  void findAllSourceDetailsTest() {
    List<SourceDetails> sourceDetails = sourceDetailsRepository.findAll();
    assertEquals(10, sourceDetails.size());
    assertEquals("SOURCING", sourceDetails.get(0).getType());
  }

  @Test
  void findSourceDetailsByTypeTest() {
    List<SourceDetails> sourceDetails = sourceDetailsRepository.findByType("SOURCING");
    assertEquals(7, sourceDetails.size());
  }

  @Test
  void findSourceDetailByCandidateIdTest() {
    Optional<Candidate> candidate = candidateRepository.findById(1000L);
    Optional<SourceDetails> sourceDetails = sourceDetailsRepository.findById(candidate.get().getSourceDetailsId());
    assertEquals(6L, sourceDetails.get().getId());
  }

}

As you can see, some SQL scripts are called in multiple classes.

Those scripts all include data in the database:

import_hierarchy_entity.sql

INSERT INTO public.hierarchy_entity(functional_code, label, short_label, type_functional_code,
                                    parent_entity_functional_code)
VALUES ('CC_EDGE_EMB', 'CC Edge & Embedded', 'EE', 'CC', 'DIL'),
       ('DPT_AD1', 'AD1', 'AD1', 'DEPT', 'CC_AUG_DATA'),
       ('DPT_UE2', 'UE2', 'UE2', 'DEPT', 'CC_USER_EFF'),
       ('DPT_UE3', 'UE3', 'UE3', 'DEPT', 'CC_USER_EFF'),
       ('DPT_UE4', 'UE4', 'UE4', 'DEPT', 'CC_USER_EFF'),
       ('DPT_UE5', 'UE5', 'UE5', 'DEPT', 'CC_USER_EFF');

import_candidate.sql

INSERT INTO public.candidate(id, first_name, last_name, sex, status, author_id, manager_id, profile_id, site_id,
                             authored_at, edited_at, is_deleted, arrival_on, years_of_experience, source_id,
                             source_details, source_details_id, dept_code, entity_code)
VALUES (1000, 'JEAN', 'Louis', 'MALE', 'PREQUALIFICATION', 'admin', 'operationnel', 1, 2, CURRENT_TIMESTAMP(),
        CURRENT_TIMESTAMP(), false, CURRENT_TIMESTAMP(), 1, 1, 'Viadeo', 6, 'DPT_EE1', 'CC_EDGE_EMB');

One thing to keep in mind, some tables have a functional_code, which is a string, as a primary key.

Here is the configuration of the Spring Boot profile used for my tests:

application.yml

spring:
  profiles: test
  datasource:
    driver-class-name: org.h2.Driver
    url: jdbc:h2:mem:db;DB_CLOSE_DELAY=-1
    username: sa
    password:
  data:
    classpath: import.sql

  jpa:
    database-platform: org.hibernate.dialect.H2Dialect
    generate-ddl: true
    hibernate:
      default_schema: public
      ddl-auto: create
      show-sql: true
  flyway:
    enabled: false

When I run the tests individually, it works fine. However, when I run the command mvn test, I get failures of the same kind:

Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: 
Intégrité référentielle violation de contrainte: "FK87J5L5FKHUUWS58S4QUJU65O6: PUBLIC.CANDIDATE FOREIGN KEY(AUTHOR_ID) REFERENCES PUBLIC.MYREC_USER(ID) ('admin')"
Referential integrity constraint violation: "FK87J5L5FKHUUWS58S4QUJU65O6: PUBLIC.CANDIDATE FOREIGN KEY(AUTHOR_ID) REFERENCES PUBLIC.MYREC_USER(ID) ('admin')"; SQL statement:
INSERT INTO public.candidate(id, first_name, last_name, sex, status, author_id, manager_id, profile_id, site_id, authored_at, edited_at, is_deleted, arrival_on, years_of_experience, source_id, source_details, source_details_id, dept_code, entity_code) VALUES (1000, 'JEAN', 'Louis', 'MALE', 'PREQUALIFICATION', 'admin', 'operationnel', 1, 2, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP(), false, CURRENT_TIMESTAMP(), 1, 1, 'Viadeo', 6, 'DPT_EE1', 'CC_EDGE_EMB') [23506-200]
[ERROR] findAllSourceDetailsTest  Time elapsed: 0.09 s  <<< ERROR!

It seems the database isn't clean when a class tries to load the SQL scripts. Also, the file import.sql doesn't seem related to this, as it doesn't load the same data, and the issue persists when I remove it.

Isn't the context supposed to be cleaned before each testing class call?

Is it possible to keep using the same scripts for multiple testing classes and avoid this situation?

score 0 · Answer 1 · answered Oct 23 '21 at 00:09

Instead of the @Sql annotation, you can annotate your test class with the following. You can have the appropriate list of sql scripts for your respective test classes.

@SqlGroup({@Sql(executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD, scripts = {"classpath:schema.sql", "import_hierarchy_entity_type.sql", "import_hierarchy_entity.sql", "import_source_details.sql", "import_candidate.sql"})})

I have added schema.sql as an example in case you want to drop and recreate the schema after each test. What the code will do is that it will execute the scripts before each test method.

NB: You can also replace @Transactional with @DataJpaTest for more JPA support.

How to prevent referential integrity constraint violation in tests?

1 Answers1