删除Hibernate Envers审计实体时的外键约束冲突解决方案咨询

实体定义代码 #

@Entity
@Table(name = "employee")
@Audited
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long id;

    @Column(name = "name")
    private String name;

    @ManyToOne
    @JoinColumn(name = "employee_id")
    private Employee employee;

}

@Entity
@Table(name = "employee_hty")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class EmployeeHty {

    @EmbeddedId
    private HtyCompositePrimaryKey htyCompositePrimaryKey;

    @Column(name = "revision_type")
    private Integer revisionType;

    @Column(name = "name")
    private String name;

    @ManyToOne
    @JoinColumn(name = "employee_id")
    private Employee employee;

}

@Embeddable
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode
public class HtyCompositePrimaryKey implements Serializable {

    @Column(name = "id")
    private Long id;

    @ManyToOne
    @JoinColumn(name = "revision_id")
    private EmployeeAuditRevision auditRevision;

}

@Entity
@Table(name = "employee_audit_revision")
@Data
@AllArgsConstructor
@NoArgsConstructor
@RevisionEntity(EmployeeRevisionListener.class)
public class EmployeeAuditRevision {

    @Id
    @GeneratedValue
    @RevisionNumber
    @Column
    private Long revisionId;

    @RevisionTimestamp
    @Column
    private Date revisionDate;

    @Column
    private String userUid;

    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;

}

数据库现有数据 #

Employee表 #

EmployeeHty表 #

revision_type含义：0=创建，1=更新，2=删除

操作需求与问题 #

需要手动执行以下SQL，删除Employee表中id=1的Jack记录，并在EmployeeHty表中添加一条删除类型的审计记录：

INSERT INTO employee_hty VALUES (1, 104, 2, 'Jack', 1);
DELETE FROM employee WHERE id = 1;

但由于employee_hty表的employee_id字段与employee表的id字段存在外键约束，执行时会因外键引用检查失败而无法完成操作。

解决方案分析 #

方案1：移除employee_hty表的外键约束（可行且推荐） #

审计表的核心作用是留存历史操作痕迹，不需要与当前的业务表（Employee）维持外键关联。当Employee记录被删除后，审计记录仍需保留删除前的关联ID（如employee_id=1）来还原历史场景，外键约束反而会阻碍这种正常的审计流程。

执行SQL删除外键约束（需替换为实际的外键约束名称，可通过SHOW CREATE TABLE employee_hty;查询）：

ALTER TABLE employee_hty DROP FOREIGN KEY [外键约束名称];

方案2：临时禁用外键检查（仅限应急，不推荐） #

先禁用数据库的外键检查，执行操作后再恢复，这种方式会临时破坏数据约束完整性，容易引发数据不一致，仅适合紧急场景：

SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM employee WHERE id = 1;
INSERT INTO employee_hty VALUES (1, 104, 2, 'Jack', 1);
SET FOREIGN_KEY_CHECKS = 1;

方案3：修改实体映射，移除审计表的外键关联（从根源解决） #

在EmployeeHty实体中，将原有的@ManyToOne关联改为普通的数值字段，仅存储employee_id的历史值，这样从JPA层面就不会生成外键约束，符合审计表的设计初衷：

// 替换原有的@ManyToOne关联代码
@Column(name = "employee_id")
private Long employeeId;

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