How to fix "could not execute statement; SQL [n/a]; constraint [null]" In OneToMany Relationship in Spring Boot

Question

I have to perform a delete operation through an API. Once I call that API it gives some nested exceptions like.

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

and

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

My Entities like

@Entity
@Table(name = "EMPLOYEE")
public class Employee {
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;
    @Id
    private String emp_id;
    
    @OneToMany(targetEntity = SalaryAllowances.class, cascade = CascadeType.ALL, orphanRemoval = true)
    @Fetch(FetchMode.SELECT)
    @JoinColumn(name = "emp_id", referencedColumnName = "emp_id")
    private List<SalaryAllowances> salaryAllowances;
    private String firstName;
    private String lastName;
//...
}

and the mapped entity is like

@Entity
@Table(name = "Allowances")
public class SalaryAllowances {
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;
    @Id
    private String allowanceId;
    private String name;
    private Double amount;
// ...
}

Here is my repository class.

@Repository
public interface EmployeeRepository extends CrudRepository<Employee, Long> {

    @Modifying
    @Query("DELETE FROM Employee e WHERE e.emp_id = :emp_id")
    void deleteEmployee(@Param("emp_id") String emp_id);

}

Can anyone help me to figure this out?

Thanks.

Why you have id and emp_id, can you show your table structure ? — Boorsuk, Sep 08 '20 at 13:57
Please: More stacktrace, more data, more param. (then the problem/solution will be obvious) But for now: the usage of `@GeneratedValue` on non-`@Id` is somewhat "special", and you obviously have a constraint violation... — xerx593, Sep 08 '20 at 13:58

score 2 · Accepted Answer · answered Sep 08 '20 at 14:01

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

This is difficult to answer without looking at full stacktrace (but assuming there should be Foreign key constraint error, because you are directly deleting parent row)

Although, you don't have to write the query to delete, use the spring data repository functions, you can simply write in your calling code:

Employee employee = .... //Assuming you have queried the object from repository

// Some validation or logic here then

employeeRepository.delete(employee)

// It will delete SalaryAllowances entities associated with this employee, since you have CascadeMode.ALL

Specifying your own query will not work, please check this answer here for more details.

Also, @GeneratedValue without @Id field will not work, please mark the field @Id and denote empId field with @NaturalId

How to fix "could not execute statement; SQL [n/a]; constraint [null]" In OneToMany Relationship in Spring Boot

1 Answers1