sql - Hibernate Postgres Delete self referencing unidirectional entity

Question

I will try to explain my issue:

I am using Spring Boot/Hibernate and the parent entity is

getters/setters and other fields are omitted

public class BaseEntity {
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

@Valid
@JsonManagedReference
@OneToMany(mappedBy = "contract", fetch = FetchType.EAGER, orphanRemoval = true, cascade = {CascadeType.MERGE, CascadeType.DETACH, CascadeType.PERSIST, CascadeType.REMOVE})
@OnDelete(action = OnDeleteAction.CASCADE)
private List<ChildEntity> myList = new ArrayList<>();

public void addChildEntityChildEntity list) {
    myList.add(list);
    list.setChildEntity(this);
}

public void removeChildEntity(ChildEntity entity) {
    myList.entity(entity);
    list.setChildEntity(null);
}
public List<ChildEntity> getChildEntitys(){
    return this.myList;
}


public class ChildEntity   {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;


@Valid
@JsonBackReference
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "baseentity_id", referencedColumnName = "id",nullable = false)
@OnDelete(action = OnDeleteAction.CASCADE)
private BaseEntity baseEntity;


//here is the self reference in the child
@Nullable
@Valid
@ManyToOne(fetch = FetchType.EAGER, cascade={CascadeType.REMOVE, CascadeType.PERSIST, CascadeType.REFRESH})
@JoinColumn(name = "reference_child_id", referencedColumnName = "id")
@OnDelete(action = OnDeleteAction.CASCADE)
private ChildEntity referenceChildEntity;


public void addChildEntity(ChildEntity childEntity) {
    this.referenceChildEntity = childEntity;
    referenceChildEntity.setChildEntity(this);
}

public void removeChildEntity() {
    referenceChildEntity.setChildEntity(null);
    referenceChildEntity = null;
}

public void addToParentEntity(BaseEntity baseEntity) {
    baseEntity.addChildEntity(this);
    this.setChildEntity(baseEntity);
}

public void removeBaseEntity() {
    this.baseEntity.removeChildEntity(this);
    this.baseEntity = null;
}

These are the entities that are important:

However when I try to delete the the BaseEntity: AND there is a relationship with ChildEntity that has other ChildEntity relationships I get a constraint error

Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "base_entity" violates foreign key constraint "reference_child_id" on table "base_entity"
  Detail: Key (id)=() is still referenced from table "base_entity".
  Where: SQL statement "delete from base_entity where id = id.id"

this is the constraint from the psql:

 CONSTRAINT_id_fkey FOREIGN KEY (reference_child_id)
        REFERENCES base_entity (id) MATCH SIMPLE
    

Finally, the delete happens in sql function that looks like:

create or replace function delete_base_entity_by_id(
  base_id int
)
  returns void as
$BODY$
declare
  loop_base base_entity;
  loop_child child_entity;
begin
  for loop_base in (select * from base_entity where original_id = (select original_id from base_entity where id = base_id) order by start_date, id desc) loop
    for loop_child in (select * from child_entity where base_id = loop_base.id order by id desc) loop
      delete from child2 where child_id = loop_child.id;
      delete from child3 where child_id = loop_child.id;
      delete from child4 where child_id = loop_child.id;
      delete from child5 where loan_child_id = loop_child.id;
      delete from child6 where loan_child_id = loop_child.id;
      delete from child7 where child_id = loop_child.id;
      delete from child8 where child_id = loop_child.id;
      delete from child9 where id = loop_child.id;
    end loop;
    delete from base_entity where id = loop_base.id;
  end loop;
  delete from child_entity where id = loop_base.loan_id;
end
$BODY$
  language plpgsql;

I have tried to add on delete cascade on the tables but there is problem with the children that i try to delete with the sql function

 alter table child_entity
drop constraint id_fkey,
add constraint id_fkey
   foreign key (reference_child_id)
    REFERENCES child_entity (id)
   on delete cascade;

Any ideas and suggestions on this really apriciated:

question from:https://stackoverflow.com/questions/66058450/hibernate-postgres-delete-self-referencing-unidirectional-entity

Answer 1

To fix this, you can use a deferred foreign key constraint.

alter table child_entity
drop constraint id_fkey,
add constraint id_fkey
   foreign key (reference_child_id)
    REFERENCES child_entity (id)
    deferrable initially deferred;

This will cause the database to enforce constraints at transaction commit rather than after the statement.