Spring boot SQL error in deleting record with not-null constraint violation

Question

In Spring boot I have two entity classes

import java.time.OffsetDateTime;
import java.util.Set;
import java.util.UUID;
import jakarta.persistence.*;

import lombok.*;
import org.apache.commons.lang3.StringUtils;
import org.cnr.plantvocdb.enums.LeafHabitus;
import org.cnr.plantvocdb.enums.PlantsRanks;

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Getter
@Setter
@Table(name = "plants_voc")
public class PlantEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    @Column(name="id", length = 50, nullable = false, updatable = false)
    private UUID id;

    @Column(name="ipni", length = 50)
    private String ipni;

    @Setter(AccessLevel.NONE)
    @Column(name="full_name_plain", length = 50)
    private String fullNamePlain;

    @Setter(AccessLevel.NONE)
    @Column(name="full_name_no_authors_plain", length = 50)
    private String fullNameNoAuthorsPlain;

    @Setter(AccessLevel.NONE)
    @Column(name="plant_name", length = 30, nullable = false)
    private String name;

    @Setter(AccessLevel.NONE)
    @Column(name="family", length = 30, nullable = false)
    private String family;

    @Setter(AccessLevel.NONE)
    @Column(name="genus", length = 30, nullable = false)
    private String genus;

    @Setter(AccessLevel.NONE)
    @Column(name="species", length = 30, nullable = false)
    private String species;

    @Column(name="valid_nomenclature")
    private boolean validNomenclature;

    @Column(name="rank", length = 20)
    @Enumerated(EnumType.STRING)
    private PlantsRanks rank;

    @Column(name="leaf_habitus", length = 20)
    @Enumerated(EnumType.STRING)
    private LeafHabitus leafHabitus;

    @OneToMany(
            fetch = FetchType.EAGER,
            cascade = CascadeType.ALL,
            mappedBy = "plant"
    )
    private Set<PlantEmitterEntity> emitter;

    @ElementCollection
    @CollectionTable(
            name = "synonyms", // Nome della tabella intermedia
            joinColumns = @JoinColumn(name = "fk_synonyms_plant_id"),
            foreignKey = @ForeignKey(name = "FK_synonyms_plant")
    )
    @Column(name="synonyms")
    private Set<String> synonyms;

    @Column(name="created_datetime_utc", updatable = false) // creation_datetime_utc
    private OffsetDateTime createdDatetimeUTC;

    @Column(name="updated_datetime_utc")  // last_modified_datetime_utc
    private OffsetDateTime updatedDatetimeUTC;

    public void setName(String name) {

        this.name = StringUtils
                .normalizeSpace(name.toLowerCase());
    }

    public void setFamily(String family) {

        this.family = StringUtils
                .normalizeSpace(StringUtils
                        .capitalize(family.toLowerCase()));
    }

    public void setGenus(String genus) {

        this.genus = StringUtils
                .normalizeSpace(StringUtils
                        .capitalize(genus.toLowerCase()));
    }

    public void setSpecies(String species) {

        this.species = StringUtils
                .normalizeSpace(species.toLowerCase());
    }

    public void setFullNamePlain(String fullNamePlain) {
        this.fullNamePlain = StringUtils
                .normalizeSpace(fullNamePlain);
    }

    public void setFullNameNoAuthorsPlain(String fullNameNoAuthorsPlain) {
        this.fullNameNoAuthorsPlain = StringUtils
                .normalizeSpace(fullNameNoAuthorsPlain);
    }
}

and

import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;

@Entity
@Table(name="emitters")
@Getter
@Setter
public class PlantEmitterEntity {

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

    @Column(name="emits", length = 50)
    private boolean emits;

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

    @ManyToOne(
            fetch = FetchType.LAZY,
            cascade = CascadeType.ALL
    )
    @JoinColumn(
            name = "fk_emitters_plant_id",
            nullable = false,
            updatable = true,
            insertable = true,
            foreignKey = @ForeignKey(name = "FK_emitters_plant"))
    private PlantEntity plant;

}

I developed a series of endpoints in the Controller in order to perform the classic GET, PUT, DELETE and UPDATE operations. However, when I try to delete a plant record using its UUID id I get this error:

org.postgresql.util.PSQLException: ERROR: null value in column "fk_emitters_plant_id" of relation "emitters" violates not-null constraint

I suppose this is due to the constraint that doesn't let me delete PlantEntity along with PlantEmitterEntity

Controller

@DeleteMapping("/{id}")
    public ResponseEntity<ResponsePlantDTO> delete(@PathVariable("id") UUID id){
        Optional<ResponsePlantDTO> optionalResponsePlantDTO = service.findPlantById(id);
        if(optionalResponsePlantDTO.isPresent()){
            service.delete(optionalResponsePlantDTO.get());
            return ResponseEntity
                    .status(HttpStatus.OK)
                    .body(optionalResponsePlantDTO.get());
        } else {
            throw new PlantNotFoundException(
                    MessageFormat.format("Plant not found with id code: {0}.", id.toString())
            );
        }
    }

Service

public void delete(ResponsePlantDTO plant){
    PlantEntity plantEntity = mapper.map(plant, PlantEntity.class);
    repository.delete(plantEntity);
}

Answer 1

You should add the orphanRemoval = true attribute to remove childs (orphans) of the deleted PlantEntity:

    @OneToMany(
            fetch = FetchType.EAGER,
            cascade = CascadeType.ALL,
            orphanRemoval = true,  // Ensures childs are deleted when plant is deleted
            mappedBy = "plant"
    )
    private Set<PlantEmitterEntity> emitter;

Besides, consider to change repository.delete method to repository.deleteById which directly issues a delete operation without requiring entity retrieval.