0

I am using PlanetScale (Database) which doesn't allow foreign keys, so I am trying to enforce referential integrity in the application instead of the database. Is there an easy and efficient way of checking for child records before deleting the Entity?

My first thought was to use use the @PreRemove annotation and check a @JoinColumn (List) if it is empty. Will this be slow and inefficient? I expect there to be many children.

Is it possible to limit the @JoinColumn only to fetch the first record since I only need to know if there are one or more records?

tchristofferson
  • 183
  • 1
  • 1
  • 14

1 Answers1

0

The solution I have found is to create a new entity listener (Component) class and autowire the dao field. Then I created a findFirstBy method on the repository and if it returns null there are no children.

tchristofferson
  • 183
  • 1
  • 1
  • 14
  • unless you also add locking, that allows child inserts to occur after your check but before your transaction completes. Short of pessimistic locking (which kills concurrent performance), you'd have to make sure you update the parent table (optimistic locking) when adding/removing children to be able to detect changes. You can then trust any parent->child *ToMany relationship and detect if it has changed before your transaction commits, just like the DB would. – Chris Oct 31 '22 at 15:03
  • I didn't think of this, thanks. Is this what the JPA @Version annotation is used for? – tchristofferson Nov 03 '22 at 23:10
  • Yes, it allows you to have JPA include the column in updates so it can detect if it was changed. – Chris Nov 04 '22 at 14:24