Hibernate - Lazily fetch @OneToOne association with same parent referred by multiple recursively mapped children

The following constellation gives me a org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException:

The associations:

1 Account   (account)      <->  (mainBudget) Budget 0..1
0..1 Budget (parentBudget) <->  (subBudget) Budget *

The actual implementation of an Account objects and Budget objects would look like this:

Forwards:

Account -> (mainBudget) Budget -> (subBudget) Budget -> (subBudget) Budget

Backwards:

Budget -> (parentBudget) Budget -> (parentBudget) Budget -> (account) Account

Each Budget in the budget tree (max height 3 as modeled) has the same account associated to it, where the account only has the mainBudget associated.

For the Account.java:

@Audited
@Entity
public class Account {
  @Id
  @GeneratedValue(strategy = GenerationType.TABLE)
  protected long id;

  @OneToOne(cascade = CascadeType.ALL, orphanRemoval=true, fetch=FetchType.LAZY) 
// Here I also tried mappedBy = "account" with @JoinColumn for the budget (with the exception stated at the end)
  @LazyToOne(LazyToOneOption.PROXY)
  private  Budget mainBudget;
  } 
...
}

For the Budget.java:

@Audited
@Entity
public class Budget {
  @Id
  @GeneratedValue(strategy = GenerationType.TABLE)
  protected long id;

  @OneToOne(fetch=FetchType.LAZY)
  //@JoinColumn(name="account_id") - I tried this but also got an exception (stated below)
  private Account account;

  @OneToMany(targetEntity = Budget.class, cascade = CascadeType.ALL, 
    mappedBy = "parentBudget", orphanRemoval=true)
  @Fetch(value = FetchMode.SUBSELECT)
  private List<Budget> subBudget; 

  @ManyToOne(fetch=FetchType.LAZY)
  @JoinColumn(name="parentBudget_id")
  private Budget parentBudget; 
...
} 

The in-memory H2 database looks the following (which seems fine to me):

The error message in detail is:

Referential integrity constraint violation: "FKovatioxlljiymn9haxf1yrjs7: PUBLIC.Account FOREIGN KEY(mainBudget_id) REFERENCES PUBLIC.Budget(id) (2259)"; SQL statement:
delete from Budget where id=? [23503-200]

The delete-method looks like this:

  @Transactional
  public boolean delete(long id, String resource)
      throws NoSuchElementException {
    List<Budget> objs = getAllById(id, resource); //SELECT * FROM Budget WHERE id=...

    if (objs.isEmpty()) {
      throw new NoSuchElementException(
          "Delete not possible.");
    }

    router.setDataSource(resource);

    for (Budget obj : objs) {
      em.remove(obj);
      em.flush(); // Here the exception is thrown
    }
    return true;
  }

Why do I get a referential integrity constraint violation exception? The same mappings worked before with EAGER loading.

As stated as comments in the code, if I use mappedBy for the parent and @JoinColumn for the child I as suggested here, I get the exception:

org.hibernate.HibernateException: More than one row with the given identifier was found: 50, for class: ...Budget

As mentioned in a comment, your Account mapping to Budget has no concept of a main or parent budget, so all budgets referencing this account are just as valid and usable for the Account->Budget reference. This is a problem in the application as a whole, but is specifically a problem when you delete an Account as there is only one budget referenced - So JPA only cascades and delete that one budget instance. You get a referential constraint exception because you've left all the others in the DB.

You can get around the deletion quirk (but I strongly advise against it) by having the DB clean up instances before you. It is DB dependent, but many have a cascade on delete option, so that they will clean up Budget rows when the account is deleted.

Better IMO is to map the relationship as it really is: A one to many.

@Entity
public class Account {
  @Id
  @GeneratedValue(strategy = GenerationType.TABLE)
  protected long id;

  @OneToMany(mappedBy = "account", cascade = CascadeType.ALL, orphanRemoval=true, fetch=FetchType.LAZY) 
  private List<Budget> budgets;
  } 
...
}

if you must have a 'main' budget reference and it must be the root parent, you can query for it directly using "select mainBudget from Budget mainBudget where mainBudget.parentBudget is null and mainBudget.account = :account". You can also maintain this in the Account directly by going over the collection when needed (but this will trigger the collection).

A non-JPA way though would be to also map it in your Account instance using Hibernate specific filtering:

@OneToOne(cascade = CascadeType.ALL, fetch=FetchType.LAZY)
@Where(clause = "parentBudget is null")
private  Budget mainBudget;

But you mentioned in comments you might need a way to mark a budget as the main one so that an Account can reference it. This is simply done by adding a foreign key from Account->budget that tracks the main one:

@OneToOne(cascade = CascadeType.ALL, fetch=FetchType.LAZY)
private Budget mainBudget;

Note though that your 'main' budget can be any budget. It is completely independent from the the Budget->Account mappings, and can be any budget in any budget graph (not necessarily a parent).

If you want JPA to clean up all Budgets when removing an Account though, you'll still need to keep a OneToMany reference from account->Budget so that JPA knows they exist and to clean them up.

If both sides (budget and account) are going to have foreign keys to the other, be sure to allow constraints to be deferred or allow fks to be nullable. Otherwise you'll get into problems with inserts, updates and deletes.

The way I see it, your model has two uni-directional relationships between Account and Budget.

It has * - 1 from Budget to Account.
It has 1 - 0..1 from Account to Budget.

I could make it work writing the following entities:

@Entity
public class Account {

    @Id
    private long id;

    @OneToOne
    @JoinColumn(name = "mainBudget_id")
    private Budget budget;
}

@Entity
public class Budget {

    @Id
    private long id;

    @ManyToOne(optional = false)
    @JoinColumn(name = "account_id")
    private Account account;

    @ManyToOne
    @JoinColumn(name = "parentBudget_id")
    private Budget parentBudget;

    @OneToMany(orphanRemoval = true, mappedBy = "parentBudget")
    private List<Budget> subBudget;
}

And using the same data you provided:

insert into Account(id, mainBudget_id) values (76, null);
insert into Budget(id, account_id, parentBudget_id) values (50, 76, null);
update Account set mainBudget_id = 50 where id = 76;
insert into Budget(id, account_id, parentBudget_id) values (51, 76, 50);
insert into Budget(id, account_id, parentBudget_id) values (52, 76, 51);
insert into Budget(id, account_id, parentBudget_id) values (55, 76, 51);
insert into Budget(id, account_id, parentBudget_id) values (58, 76, 51);
insert into Budget(id, account_id, parentBudget_id) values (61, 76, 51);
insert into Budget(id, account_id, parentBudget_id) values (64, 76, 50);
insert into Budget(id, account_id, parentBudget_id) values (65, 76, 64);
insert into Budget(id, account_id, parentBudget_id) values (68, 76, 50);
insert into Budget(id, account_id, parentBudget_id) values (69, 76, 68);
insert into Budget(id, account_id, parentBudget_id) values (72, 76, 50);
insert into Budget(id, account_id, parentBudget_id) values (73, 76, 72);
insert into Budget(id, account_id, parentBudget_id) values (2259, 76, 50);

This was enough to play around retrieving a list of budgets, deleting a budget, etc.

ATTENTION: Using the mapping provided here, you candelete any Budget in the tree. If the deleted budget isn't a leaf node, the persistence provider will delete the tree below it. Nevertheless, if you try to delete the main budget without updating the account entity first, it will fail.