Spring boot : Referential integrity constraint violation

Question

Hello I'm facing this error and none of the solution I found worked. I have a Cart entity :

@Data
@Entity
public class Cart {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  private String status;
  @ManyToOne(cascade = CascadeType.MERGE)
  @JoinColumn(name = "usr_id", nullable = false)
  private User usr;
  @ElementCollection(targetClass=String.class)
  private List<String> productsBarcode = new ArrayList<>();
  private long nutritional_score;
}

And a User entity

@Entity
@Data
public class User {
    @Id
    @GeneratedValue
    private Long id;
    private String firstname;
    private String lastname;
    private String email;
    private String phone;
    @JsonIgnore
    @OneToMany(mappedBy = "id",cascade=CascadeType.ALL)
    private List<Cart> carts = new ArrayList<Cart>();
}

When I try to pass a POST request with the following JSON :

{
  "status": "in-progress",
  "usr": {
    "id": 1,
    "firstname": "jhon",
    "lastname": "koer",
    "email": "[email protected]",
    "phone": "078542163"
  },
  "productsBarcode": [
    "3029330003533",
    "3029330003533"
  ]
}

But I get this error :

org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: 
"FK8TNAV1OMIP1EBGI23DBBO8B8T: PUBLIC.CART FOREIGN KEY(USR_ID) REFERENCES PUBLIC.USER(ID) (100)"; SQL statement: 
insert into cart (id, nutritional_score, status, usr_id) values (null, ?, ?, ?) [23506-197]

How do I fix this please ?

Answer 1

You are receiveing a DataIntegrityViolationException most probably because you may be referencing a User that is not on the database:

You can see the scenario on below test:

@Test(expected = DataIntegrityViolationException.class)
  public void saveCartWithNonExistentUserMustThrowIntegrityException() {
    // We create a bean to a user that has not been inserted on database
    User user = new User(4815L, "Alfonso", "Cuaron", "[email protected]");

    Cart cart = new Cart("in-progress", user, Arrays.asList("1234", "1234"), 10);
    Cart cartSaved = cartRepository.save(cart); // Exception is thrown
  }

  @Test
  public void saveCartWithExistentUserMustSucceed() {
    User user = new User("Alfonso", "Cuaron", "[email protected]");
    // We ensure user is persisted on database
    User existentUser = userRepository.save(user);
    // Now User.id reference exist in database
    Cart cart = new Cart("in-progress", existentUser, Arrays.asList("1234", "1234"), 10);
    Cart cartSaved = cartRepository.save(cart);

    Assert.assertNull(cartSaved.getId()); // Cart is persisted
  }