Spring Data JPA删除Company时外键约束冲突问题解决

删除Company实体时触发数据完整性约束错误：

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

需求是删除Company时自动删除所有关联的User及对应的LeaveDay记录，已在关联上配置CascadeType.ALL和orphanRemoval = true但问题依旧。

实体关联代码 #

Company实体 #

@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实体 #

@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实体 #

@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;
}

1. 确保删除时加载关联的User集合 #

JPA的级联操作依赖持久化上下文中的关联实体，如果删除Company时没有加载其关联的users集合，级联删除不会触发。修改查询逻辑，使用JOIN FETCH显式加载用户：

// 在CompanyRepository中添加查询方法
@Query("SELECT c FROM Company c JOIN FETCH c.users WHERE c.id = :id")
Optional<Company> findByIdWithUsers(@Param("id") Long id);

// 删除时使用该方法
companyRepository.findByIdWithUsers(companyId).ifPresent(company -> {
    companyRepository.delete(company);
});

2. 修正User实体中approvers关联的级联策略 #

User的approvers是多对多关联，当前配置CascadeType.ALL会导致删除User时尝试级联删除其审批人，但这些审批人可能属于其他公司，触发额外的约束冲突。将级联策略改为仅支持新增和更新：

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

3. （可选）数据库层面配置外键级联删除 #

如果希望数据库层面也能处理级联删除（避免应用层未加载关联导致的错误），可以在生成表时为外键添加ON DELETE CASCADE：

// 在User的@ManyToOne中
@ManyToOne
@JoinColumn(name = "company_id", nullable = false, 
        foreignKey = @ForeignKey(foreignKeyDefinition = "FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE"))
private Company company;

// 在LeaveDay的@ManyToOne中
@ManyToOne
@JoinColumn(name = "user_id", nullable = false,
        foreignKey = @ForeignKey(foreignKeyDefinition = "FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE"))
private User user;

注意：如果使用Hibernate自动建表，需要确保hibernate.hbm2ddl.auto设置为update或create（生产环境不建议create）。

验证删除逻辑 #

完成上述修改后，删除Company时会触发以下流程：

1. 删除Company的同时，级联删除其关联的所有User
2. 每个User被删除时，级联删除其关联的所有LeaveDay
3. 多对多关联user_approvers中的对应记录会被自动删除（因为外键指向的User已被删除）

内容的提问来源于stack exchange，提问作者Ballazx