使用JoinTable清除OneToOne关联时触发外键约束违反问题排查

我定义了如下两个实体：

@Entity
public class RoomEntity {
    @OneToOne
    @JoinTable(
        name = "room_camera_association",
        joinColumns = {@JoinColumn(name = "room_id")},
        inverseJoinColumns = {@JoinColumn(name = "camera_id")}
    )
    private CameraEntity camera;
}

以及：

@Entity
public class CameraEntity {
    @OneToOne(mappedBy = "camera")
    private RoomEntity room;
}

关联表定义如下：

CREATE TABLE room_camera_association
(
    camera_id UUID NOT NULL,
    room_id UUID NOT NULL,
    PRIMARY KEY (camera_id, room_id),
    CONSTRAINT fk_camera FOREIGN KEY (camera_id) REFERENCES camera(id),
    CONSTRAINT fk_room FOREIGN KEY (room_id) REFERENCES room(id),
    CONSTRAINT uk_rca_camera_room UNIQUE (camera_id, room_id)
);

需求为删除Room时不删除Camera，删除Camera时也不删除Room。

执行以下代码清除关联：

for (RoomEntity room: getRooms()) {
    CameraEntity camera = room.getCamera();
    if (camera != null) {
        log.info("Removing camera {} association for room {}", camera.getName(), room.getName());
        room.setCamera(null);
        camera.setRoom(null);
        roomRepository.save(room);
        cameraRepository.save(camera);
    }
}

随后执行：

roomRepository.delete(room);

触发如下异常：

2023-11-16 12:50:10.959 ERROR 1641464 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: update or delete on table "room" violates foreign key constraint "fk_room" on table "room_camera_association"
  Detail: Key (id)=(385a510c-bd26-4f89-8077-6e6b9aa78e31) is still referenced from table "room_camera_association".

请问我忽略了什么？

原因分析 #

你忽略了关联表room_camera_association中的记录没有被删除。在双向@OneToOne通过关联表实现的场景中，仅设置实体关联为null并保存实体，Hibernate不会自动删除关联表中的对应记录——因为关联表是由RoomEntity端维护的，但仅修改room.setCamera(null)只会标记实体状态变更，Hibernate不会主动清理关联表的中间数据。

解决方案 #

有两种可行的处理方式：

方式一：手动删除关联表记录 #

添加对关联表的操作，在清除实体关联后，删除对应关联表记录：

// 假设你有roomCameraAssociationRepository来操作关联表
for (RoomEntity room: getRooms()) {
    CameraEntity camera = room.getCamera();
    if (camera != null) {
        log.info("Removing camera {} association for room {}", camera.getName(), room.getName());
        // 先删除关联表记录
        roomCameraAssociationRepository.deleteByRoomIdAndCameraId(room.getId(), camera.getId());
        // 再清除实体关联并保存
        room.setCamera(null);
        camera.setRoom(null);
        roomRepository.save(room);
        cameraRepository.save(camera);
    }
}

方式二：配置关联的级联移除规则 #

修改RoomEntity中的@OneToOne注解，添加@OnDelete(action = OnDeleteAction.CASCADE)到@JoinColumn上，这样当删除Room或清除关联时，Hibernate会自动删除关联表中的对应记录：

@Entity
public class RoomEntity {
    @OneToOne
    @JoinTable(
        name = "room_camera_association",
        joinColumns = {
            @JoinColumn(
                name = "room_id",
                onDelete = OnDeleteAction.CASCADE // 添加此配置
            )
        },
        inverseJoinColumns = {@JoinColumn(name = "camera_id")}
    )
    private CameraEntity camera;
}

注意：这个配置会让数据库层面在删除Room时自动删除关联表中对应的记录，同时当你设置room.setCamera(null)并保存时，Hibernate也会触发关联表记录的删除，无需手动操作。

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