foreign key constraint violation when trying to clear a OneToOne relationship using a JoinTable

Question

I have the following two entities:

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

...and:

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

... with the association table being defined like so:

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)
);

When I delete a room, I don't want to delete a camera. Likewise, if I delete a camera I don't want to delete a room.

When I execute the following code:

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

... and then subsequently do:

roomRepository.delete(room);

... I get the following exception:

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".

What am I missing?

Answer 1

I figured out what was happening. We also have triggers setup to audit data that is being modified or deleted. The trigger function was setup like so:

INSERT INTO camera_association_log
    VALUES (NEW.*, 'DELETE'); RETURN NEW;

... and the trigger was BEFORE DELETE ON.

This was effectively preventing the deletes from happening, but this was happening silently. When I switched the function to RETURN OLD and the trigger to AFTER DELETE ON, this code started working:

  room.setCamera(null);
  camera.setRoom(null);
  roomRepository.save(room);
  cameraRepository.save(camera);

... automatically removing the record from the association table and allowing the subsequent room delete to work.