Delete and Create Records Violates Constraints in Spring Data Jpa

Still haven’t find a decent answer in StackOverFlow.

Scenario: I have an Attribute entity, which has multipul children Options. 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:

  1. A normal implementation, what I expect is that JPA handles this, deletes the old record in Database and inserts a new record.

  2. Run a delete method before inserting.

  3. Annotate the deleting mehtod with @Transactional, then implement the insert.

  4. 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 instert before deleting so when we have parent child contraints it will not work, so we added the deferred constraint so it will delete and insert then apply the contraint rules. This will only work for prostgress and Oracle future dbs be aware.

References