Delete then create records are causing a duplicate key violation with Spring Data JPA

Hold on to your hat, as this is a rather long explanation, but when I look at your code, it looks like you are missing a couple of key concepts about how JPA works.

First, adding Entities to a collection or removing entities from a collection does not mean that that the same operation will occur in the database, unless a persistence operation is propagated using cascadeding or orphanRemoval.

For an entity to be added to the database, you must call EntityManager.persist() either directly, or through cascading persist. This is basically what happens inside JPARepository.save()

If you wish to remove an entity, you must call EntityManager.remove() directly or by cascading the operation, or through JpaRepository.delete().

If you have a managed entity (one that is loaded into a persistence context), and you modify a basic field (non-entity, non-collection) inside a transaction, then this change is written to the database when the transaction commits, even if you did not call persist/save. The persistence context keeps a internal copy of every loaded entity, and when a transaction commits it loops through the internal copies and compares to the current state, and any basic filed changes triggers an update query.

If you have added a new Entity (A) to a collection on another entity (B), but have not called persist on A then A will not be saved to the database. If you call persist on B one of two things will happen, if the persist operation is cascaded, A will also be saved to the database. If persist is not cascaded you will get an error, because a managed entity refers to an unmanaged entity, which give this error on EclipseLink: "During synchronization a new object was found through a relationship that was not marked cascade PERSIST". Cascade persist makes sense because you often create a parent entity and it's children at the same time.

When you want to remove an Entity A from a collection on another Entity B, you can't rely on cascading, since you are not removing B. Instead you have to call remove on A directly, removing it from the collection on B does not have any effect, as no persistence operation has been called on the EntityManager. You can also use orphanRemoval to trigger delete, but I would advise you to be careful when using this feature, especially since you seem to be missing some basic knowledge about how persistence operations work.

Normally it helps to think about the persistence operation, and which entity it must be applied to. Here is how the code would have looked if I had written it.

@Transactional
public void create(Integer id, List<Integer> customerIDs) {

    Header header = headerService.findOne(id);
    // header is found, has multiple details

    // Remove the details
    for(Detail detail : header.getDetails()) {
        em.remove(detail);
    }

    // em.flush(); // In some case you need to flush, see comments below

    // Iterate through list of ID's and create Detail with other objects
    for(Integer id : customerIDs) {
        Customer customer = customerService.findOne(id);

        Detail detail = new Detail();
        detail.setCustomer(customer);
        detail.setHeader(header);  // did this happen inside you service?
        em.persist(detail);
    }
}

First there is no reason to persist the Header, it is a managed entity and any basic field you modify will be change when the transaction commits. Header happens to be the foreign key for the Details entity, which means the important thing is detail.setHeader(header); and em.persist(details), since you must set all foreign relations, and persist any new Details. Likewise, removing existing details from a Header, has nothing to do with the Header, the defining relation (foreign key) is in Details, so removing details from the persistence context is what removes it from the database. You can also use orphanRemoval, but this require additional logic for each transaction, and In my opinion the code is easier to read if each peristence operation is explicit, that way you don't need to go back to the entity to read the annotations.

Finally: The sequence of persistence operation in your code, does not transalte to the order of queries executed against the database. Both Hibernate and EclipseLink will insert new entities first, and then delete existing entities. In my experience this is the most common reason for "Primary key already exist". If you remove an entity with a specific primary key, and then add a new entity with the same primary key, then the insert will occur first, and cause a key violation. This can be fixed by telling JPA to flush the current Persistence state to the database. em.flush() will push the delete queries to the database, so you can insert another row with the same primary key as one you have deleted.

That was a lot of information, please let me know if there was anything you did not understand, or need me to clarify.

The cause is described by @klaus-groenbaek but I noticed something funny while working around it.

While using the Spring JpaRepository I was not able to get this to work when using a derived method.

So the following does NOT work:

void deleteByChannelId(Long channelId);

But specifying an explicit (Modifying) Query makes it work correctly, so the following works:

@Modifying
@Query("delete from ClientConfigValue v where v.channelId = :channelId")
void deleteByChannelId(@Param("channelId") Long channelId);

In this case the statements are committed / persisted in the correct order.