删除Session实体触发外键约束冲突问题求助

问题描述 #

我建模了Session和Speaker两个实体，二者为多对多关联关系。尝试删除Session实例时，数据库报错：

update or delete on table "sessions" violates foreign key constraint "session_schedule_session_id_fkey"

已在Session实体的@ManyToMany注解上添加@OnDelete(action = OnDeleteAction.CASCADE)但无效（已知该注解不建议用于多对多关系）。

相关代码 #

Session实体 #

@Entity(name = "sessions")
public class Session {
    // 属性未使用驼峰命名是为了匹配数据库表字段，无需额外@Column注解
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long session_id;
    private String session_name;
    private String session_description;
    private String session_length;
    
    @OnDelete(action = OnDeleteAction.CASCADE)
    @ManyToMany()
    @JoinTable(
            name = "session_speakers",
            joinColumns = @JoinColumn(name = "session_id"),
            inverseJoinColumns = @JoinColumn(name = "speaker_id")
    )
    private List<Speaker> speakers;

    public Session() {
    }

    // 省略getter/setter
}

Speaker实体 #

@Entity(name = "speakers")
public class Speaker {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long speaker_id;

    private String first_name;
    private String last_name;
    private String title;
    private String company;
    private String speaker_bio;

    @Lob
    @Type(type = "org.hibernate.type.BinaryType")
    private Byte[] speaker_photo;

    public Byte[] getSpeaker_photo() {
        return speaker_photo;
    }

    public void setSpeaker_photo(Byte[] speaker_photo) {
        this.speaker_photo = speaker_photo;
    }

    @ManyToMany(mappedBy = "speakers")
    @JsonIgnore// 解决序列化问题
    private List<Session> sessions;

    // 省略getter/setter
}

删除接口代码 #

@RequestMapping(value = "{id}", method = RequestMethod.DELETE)
public void delete(@PathVariable Long id){
    sessionRepo.deleteById(id);
}

解决方案 #

原因分析 #

@OnDelete注解仅适用于一对多等单表关联场景，无法作用于多对多的中间关联表。删除Session时，数据库中间表session_speakers中仍存在关联该Session的记录，触发外键约束错误。

可行方案 #

1. 手动清理关联关系再删除

删除Session前，先清空该Session对应的Speaker关联集合，触发Hibernate删除中间表记录，之后再删除Session：

@RequestMapping(value = "{id}", method = RequestMethod.DELETE)
public void delete(@PathVariable Long id){
    Session session = sessionRepo.findById(id)
            .orElseThrow(() -> new RuntimeException("Session不存在"));
    session.getSpeakers().clear(); // 清空关联，删除中间表对应记录
    sessionRepo.save(session);
    sessionRepo.deleteById(id);
}

2. 配置JPA级联操作

在Session的@ManyToMany注解中添加级联删除配置，让Hibernate自动处理中间表记录：

@ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
@JoinTable(
        name = "session_speakers",
        joinColumns = @JoinColumn(name = "session_id"),
        inverseJoinColumns = @JoinColumn(name = "speaker_id")
)
private List<Speaker> speakers;

该配置仅会删除中间表中关联当前Session的记录，不会删除Speaker实体（若Speaker还被其他Session关联）。

3. 数据库层面设置外键级联删除

直接在数据库的session_speakers表中，将session_id字段的外键约束设置为ON DELETE CASCADE。这样删除sessions表记录时，数据库会自动清理中间表的关联记录，无需修改代码。

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