JPA配置外键ON DELETE SET NULL解决删除父行约束报错

当前场景下，ratings表中的answer_id为外键，关联answers表的主键answer_id。尝试删除answers表中的记录时，系统抛出如下错误：

"Cannot delete or update a parent row: a foreign key constraint fails."
（无法删除或更新父行：外键约束失败）
需要为该外键配置ON DELETE SET NULL属性，现有JPA实体类映射代码如下：

Ratings实体：

@Entity
@Table(name = "ratings")
public class Ratings {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long rating_id;

    @Column(nullable = false, unique = false, length = 45)
    private Short ratingValue;

    @ManyToOne(cascade = fetch = FetchType.LAZY)
    @JoinColumn(name = "userId")
    private User user;

    @ManyToOne(cascade = fetch = FetchType.LAZY)
    @JoinColumn(name = "answer_id")
    private Answer answer;

    @ManyToOne(cascade = fetch = FetchType.LAZY)
    @JoinColumn(name = "question_id")
    private Question question;
//getters and setters
}

Answer实体：

@Entity
@Table(name = "answers")
public class Answer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long answer_id;

    @Column(nullable = false, unique = false, length = 100)
    private String fullAnswer;

    /*Many to one mapping question*/
    @ManyToOne(cascade = FetchType.LAZY)
    @JoinColumn(name = "question_id")
    private Question question;

    /* Many to One mapping with users*/
    @ManyToOne(cascade = FetchType.LAZY)
    @JoinColumn(name = "userId")
    private User user;
//getters and setters
}

首先修正现有代码的注解语法错误，再配置外键的删除规则：

1. 修正@ManyToOne的错误写法：现有代码把fetch属性错误写在了cascade属性位置，cascade用于配置实体级联操作策略，fetch用于配置关联加载策略，二者是独立属性。如果不需要配置级联操作，直接删除cascade = 的错误写法，仅保留fetch = FetchType.LAZY即可，否则项目启动会报注解参数类型不匹配错误。
2. 配置外键ON DELETE SET NULL规则：修改Ratings类中answer字段的@JoinColumn注解，添加两个核心配置：
   • 设置nullable = true，允许answer_id外键列存储NULL值，这是SET NULL规则生效的前提
   • 通过foreignKey属性自定义外键定义，明确指定ON DELETE SET NULL规则

修改后的answer关联代码如下：

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(
    name = "answer_id",
    nullable = true,
    foreignKey = @ForeignKey(
        name = "fk_ratings_answer",
        foreignKeyDefinition = "FOREIGN KEY (answer_id) REFERENCES answers(answer_id) ON DELETE SET NULL"
    )
)
private Answer answer;

补充说明 #

• 如果使用Hibernate自动维护表结构（配置spring.jpa.hibernate.ddl-auto=update/create/create-drop），修改代码后重启服务即可自动更新外键约束。
• 如果是手动管理数据库表结构，需要单独执行SQL更新已有外键：

-- 先删除原有外键，将约束名替换为当前库中实际的外键名称
ALTER TABLE ratings DROP FOREIGN KEY 原有外键约束名;
-- 新增带ON DELETE SET NULL规则的外键
ALTER TABLE ratings ADD CONSTRAINT fk_ratings_answer FOREIGN KEY (answer_id) REFERENCES answers(answer_id) ON DELETE SET NULL;

• 其余@ManyToOne关联如果需要配置级联删除规则，也可以按照相同逻辑调整。

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