Delete and Create Records Violates Constraints in Spring Data Jpa
Still haven’t found a decent answer in StackOverFlow.
Scenario: I have an Attribute entity, which has multiple children Options. The option has its unique keys, say, label. When I delete an Option with label “AAA”, with Id 1, then add a new Option without Id, but the same label. Then, hit save.
What I’ve tried, however, doesn’t work:
-
A normal implementation, what I expect is that JPA handles this, deletes the old record in Database and inserts a new record.
-
Run a delete method before inserting.
-
Annotate the deleting method with
@Transactional, then implement the insert. -
After deleting, run
repository.flush().
What saved me:
ALTER TABLE options
ADD CONSTRAINT your_constraint_name UNIQUE (id, label) DEFERRABLE INITIALLY DEFERRED;
Explain:
JPA preference is to insert before deleting so when we have parent-child constraints it will not work, so we added the deferred constraint so it will delete and insert then apply the constraint rules. This will only work for Postgres and Oracle future dbs be aware.