Referential integrity constraint violation

Question

in my spring-boot application, I have a feature to add multiple animals into a single room. To achieve that I created my entity class like below

@Entity
@Data
@NoArgsConstructor
@FieldDefaults(level = AccessLevel.PRIVATE)
@EntityListeners(AuditingEntityListener.class)
public class Animal {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_animal_id")
    @SequenceGenerator(name = "seq_animal_id")
    Long id;

    @NotEmpty(message = "title should be given")
    String title;

    @CreatedDate
    LocalDateTime located;

    String type;
    Long preference;

    @OneToMany(mappedBy = "animal")
    Set <Favorite> favoriteRooms;

    @ManyToOne(cascade = CascadeType.MERGE)
    @JoinColumn(name = "room_id")
    Room room;
}

below is my room entity:

@Entity(name = "room")
@Data
@NoArgsConstructor
@FieldDefaults(level = AccessLevel.PRIVATE)
@EntityListeners(AuditingEntityListener.class)
public class Room {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_room_id")
    @SequenceGenerator(name = "seq_room_id")
    Long id;

    String title;
    Long size;

    @CreatedDate
    LocalDateTime createdOn;

    @OneToMany(mappedBy = "rooms")
    Set<Favorite> favorites;

    @OneToMany(mappedBy = "room")
    Set<Animal> animals;

}

Below my service and controller class:

@RestController
@AllArgsConstructor
@FieldDefaults(level = AccessLevel.PRIVATE)
@RequestMapping("/animal")
public class AnimalController {

    AnimalService animalService;

    @PostMapping("/add")
    public AnimalDto addAnimal(@Valid @RequestBody AnimalDto animalDto){
        return animalService.add(animalDto);
    }
}

Animal Service

@Service
@AllArgsConstructor
@FieldDefaults(level = AccessLevel.PRIVATE)
@Slf4j
public class AnimalService {

    AnimalRepository animalRepository;
    RoomRepository roomRepository;
    SourceToDestinationMapper mapper;

    public AnimalDto add(AnimalDto animalDto) {
        Animal animal = mapper.animalDtoToAnimal(animalDto);
        //isValidAnimal(animal);

        Animal savedAnimal = animalRepository.save(animal);
        log.debug("animal object saved:",animal);
        return mapper.animalToAnimalDto(savedAnimal);
    }
}

Mapper class:

@Mapper(componentModel = "spring")

public interface SourceToDestinationMapper {

    Animal animalDtoToAnimal(AnimalDto animal);
    AnimalDto animalToAnimalDto( Animal animal);

    List<AnimalDto> animalsToAnimalDtos(List<Animal> animals);

    Room roomDtoToRoom(RoomDto roomDto);

    RoomDto roomToRoomDto(Room room);
}

but if I am trying to add animals in the same room like below

{"id":null,
"title":"Zebra",

"type":null,
"preference":null,
"favoriteRooms":null,
"room": {"id": 1,
    "title": "Green",
    "size": 50,
    "createdOn": null,
    "favorites": null,
    "animals": null}
}

in my subsequent request to add an animal throws constraint violation exception like below.

org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Referential integrity constraint violation: "FKLCRIG41DUY7EQV30DYTSR4L78: PUBLIC.ANIMAL FOREIGN KEY(ROOM_ID) REFERENCES PUBLIC.ROOM(ID) (CAST(1 AS BIGINT))"; SQL statement:
insert into animal (located, preference, room_id, title, type, id) values (?, ?, ?, ?, ?, ?) [23506-212]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:508) ~[h2-2.1.212.jar:2.1.212]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:477) ~[h2-2.1.212.jar:2.1.212]
    at org.h2.message.DbException.get(DbException.java:223) ~[h2-2.1.212.jar:2.1.212]
    at org.h2.message.DbException.get(DbException.java:199) ~[h2-2.1.212.jar:2.1.212]
    at org.h2.constraint.ConstraintReferential.checkRowOwnTable(ConstraintReferential.java:311) ~[h2-2.1.212.jar:2.1.212]
    at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:252) ~[h2-2.1.212.jar:2.1.212]
    at org.h2.table.Table.fireConstraints(Table.java:1177) ~[h2-2.1.212.jar:2.1.212]
    at org.h2.table.Table.fireAfterRow(Table.java:1195) ~[h2-2.1.212.jar:2.1.212]

Edit 1: I updated my service class like this. but it is not updating room values:

public AnimalDto add(AnimalDto animalDto) throws InvalidRoomDetailException {
        Animal animal = mapper.animalDtoToAnimal(animalDto);
        isValidAnimal(animal);
        Animal savedAnimal = animalRepository.save(animal);
        log.debug("animal object saved:",savedAnimal);
        updateRoomValues(animalDto, savedAnimal);
        return mapper.animalToAnimalDto(savedAnimal);
    }

    private void updateRoomValues(AnimalDto animalDto, Animal savedAnimal) {
        if(animalDto.getRoom() == null)
            return;
        Room room = mapper.roomDtoToRoom(animalDto.getRoom());
        Optional<Room> optionalRoom = roomRepository.findById(room.getId());
        if(optionalRoom.isPresent()){
            Room existingRoom = optionalRoom.get();
            existingRoom.getAnimals().add(savedAnimal);
            roomRepository.save(optionalRoom.get());
            savedAnimal.setRoom(room);
        }
    }

Edit 2: After updating the code I see the below response.

{
    "id": 1,
    "title": "dog",
    "located": "2022-05-24T18:07:06.4478948",
    "type": null,
    "preference": null,
    "favoriteRooms": null,
    "room": null
}

and DB records:

Answer 1

Room is the owner side of your relationship between Animal and Room entities -it is a one-to-many relationship anyway-, so you should manage the Animal objects in a room by the Room side of the relationship.

What you should do is execute the following steps in the given order:

• Create the Animal entity on DB, without any room info, save it & fetch it.
• Get the Room entity that you want to add your Animal entity into (from db).
• Add the Animal entity into the "animals" list of the Room entity.
• Update the room entity on DB (save it as we added the animal into it)

Notice that your mapper method should create the Animal without any Room information.

Edit1:

You should not set Room of the Animal you have in hand because the managing side is the Room. The following line is not correct and should be removed.

savedAnimal.setRoom(room);

After all the steps I listed, you should fetch Animal entity from DB one last time to get the current state. Your service code will be the following:

public AnimalDto add(AnimalDto animalDto) throws InvalidRoomDetailException {
    Animal animal = mapper.animalDtoToAnimal(animalDto);
    isValidAnimal(animal);
    animal = animalRepository.save(animal);
    log.debug("animal object saved:",savedAnimal);
    updateRoomValues(animalDto, savedAnimal);
    animal = animalRepository.findById(animal.getId());
    return mapper.animalToAnimalDto(animal);
}

private void updateRoomValues(AnimalDto animalDto, Animal savedAnimal) {
    if(animalDto.getRoom() == null)
        return;
    Room room = mapper.roomDtoToRoom(animalDto.getRoom());
    Optional<Room> optionalRoom = roomRepository.findById(room.getId());
    if(optionalRoom.isPresent()){
        Room existingRoom = optionalRoom.get();
        existingRoom.getAnimals().add(savedAnimal);
        roomRepository.save(optionalRoom.get());
    }
}