Spring Data JPA: Foreign Key Constraint Violation on Delete

I have a Company entity that is related to a User entity, and the Users are linked to one or more LeaveDay entities. When I try to delete a Company entity, I get the following error message:

ERROR 7300 --- [leave_tracker_postg] [nio-8080-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.DataIntegrityViolationException: could not execute statement [ERROR: update or delete on table "companies" violates foreign key constraint "fkin8gn4o1hpiwe6qe4ey7ykwq7" on table "users" Detail: Key (id)=(1) is still referenced from table "users".] [delete from companies where id=?]; SQL [delete from companies where id=?]; constraint [fkin8gn4o1hpiwe6qe4ey7ykwq7]] with root cause

I’m trying to delete the Company entity and remove all related data like the User and LeaveDay records. Below are the entities and relationships in my application:

Company entity:

@Data
@Entity
@Table(name = "companies")
@EntityListeners(AuditingEntityListener.class)
public class Company {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @NotEmpty
    private String companyName;

    @NotEmpty
    private String country;

    private String state;

    private String zip;

    @NotEmpty
    private String address;

    private String phone;

    @Enumerated(EnumType.ORDINAL)
    private PackageType packageType;

    @OneToMany(mappedBy = "company", cascade = CascadeType.ALL, orphanRemoval = true)
    @JsonManagedReference
    private Set<User> users = new HashSet<>();

    @CreatedDate
    @Column(updatable = false)
    private LocalDateTime createdTimeStamp;

    @LastModifiedDate
    @Column(insertable = false)
    private LocalDateTime updatedTimeStamp;
}

User entity:

@Data
@Entity
@Table(name = "users")
@EntityListeners(AuditingEntityListener.class)
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @Column(nullable = false)
    private String email;

    @Column(nullable = false)
    private String password;

    @Column(nullable = false)
    private String firstName;

    @Column(nullable = false)
    private String lastName;

    private String middleName;

    private String position;

    @ManyToMany(fetch = FetchType.EAGER)
    @Enumerated(EnumType.STRING)
    private Set<Role> roles = new HashSet<>();

    private Date birthDate;

    @ManyToOne
    @JoinColumn(name = "company_id", nullable = false)
    @JsonBackReference
    private Company company;

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
    @JsonManagedReference
    private Set<LeaveDay> leaveDays = new LinkedHashSet<>();

    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(
            name = "user_approvers",
            joinColumns = @JoinColumn(name = "user_id"),
            inverseJoinColumns = @JoinColumn(name = "approver_id")
    )
    private Set<User> approvers = new HashSet<>();

    @CreatedDate
    @Column(updatable = false, nullable = false)
    private LocalDateTime createdTimeStamp;

    @LastModifiedDate
    @Column(insertable = false)
    private LocalDateTime updatedTimeStamp;
}

LeaveDay entity:

@Data
@Entity
@Table(name = "leave_days")
@EntityListeners(AuditingEntityListener.class)
public class LeaveDay {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    @Column(nullable = false)
    private LocalDate date;

    @ManyToOne
    @JoinColumn(name = "user_id", nullable = false)
    @JsonBackReference
    private User user;

    @ManyToOne
    @JoinColumn(name = "approved_by_user_id")
    private User approvedBy;

    private String comment;

    @Enumerated(EnumType.ORDINAL)
    @Column(nullable = false)
    private LeaveDayType leaveDayType;

    @Enumerated(EnumType.ORDINAL)
    @Column(nullable = false)
    private LeaveDayStatus leaveDayStatus = LeaveDayStatus.PENDING;

    @CreatedDate
    @Column(name = "created_timestamp", updatable = false, nullable = false)
    private LocalDateTime createdTimeStamp;

    @LastModifiedDate
    @Column(name = "updated_timestamp", insertable = false)
    private LocalDateTime updatedTimeStamp;
}

What’s the best way to ensure that when a Company is deleted, the associated User records and their LeaveDay records are also deleted? I’ve tried using CascadeType.ALL and set orphanRemoval = true on the relationships, but the problem persists. How can I ensure the related records are properly deleted when deleting a Company?

Using @OneToMany(mappedBy = "company") creates a uni-directional mapping and is (I think) your problem.

Try switching the relationship so that the Company is the "owner" and the User has the mappedBy in the relationship and see if that fixes it.

You can also do bi-directional if you want that - see here:

JPA JoinColumn vs mappedBy