Duplicate entry 'string1-string2' for key 'PRIMARY'

Question

In a Spring MVC application using hibernate and jpa over a MySQL database, I am getting the following error message about a child entity whenever I try to save a parent entity that includes the child entity:

Duplicate entry 'string1-string2' for key 'PRIMARY'  

Here, string1 and string2 refer to the two parts of the composite primary key of the child entity. How do I resolve this error?

Here is the way that the relationship between the entities is defined in the parent Address entity:

@ManyToOne(cascade = { CascadeType.ALL }, fetch=FetchType.EAGER)
@JoinColumns({ @JoinColumn(name = "usecode", referencedColumnName = "code", insertable = false, updatable = false),
        @JoinColumn(name = "usecodesystem", referencedColumnName = "codesystem", insertable = false, updatable = false)
})
public HL7GeneralCode use;

Here is the way the relationship is defined in the child GeneralCode entity:

@OneToMany(mappedBy = "use", cascade = {CascadeType.ALL})
private Set<HL7Address> addresses;

The complete stack trace can be viewed by clicking on this link.
The complete code for the Address entity can be found at this link.

The complete code for the GeneralCode entity can be read at this link.

The code for the composite primary key class can be found at this link.
And the BaseEntity class that is extended by Address can be found at this link.

I have read many postings on this error message. The answers to the other postings do not resolve my error message, and they generally do not address the fact that my entity uses a composite primary key.

---

EDIT:

The code for persisting the address is:

@Override
public void savehl7Address(HL7Address addr) {
    if ((Integer)addr.getId() == null) {
        System.out.println("[[[[[[[[[[[[ about to persist address ]]]]]]]]]]]]]]]]]]]]");
        this.em.persist(addr);}
    else {
        System.out.println("]]]]]]]]]]]]]]]]]] about to merge address [[[[[[[[[[[[[[[[[[[[[");
        this.em.merge(addr);}
}

---

SECOND EDIT:

I tried to follow @Ben75's advice, but the code is crashing at the line this.em.persist(addr.getUse());. Note that his if clause did not fit my actual object model, so I changed the if clause below to if(addr.getUse() != null && addr.getId()==null). Here is my code.

@Override
public void savehl7Address(HL7Address addr) {
    if(addr.getUse() != null && addr.getId()==null){
        //this next line prints in the stack trace right before the app crashes
        System.out.println("about to this.em.persist(addr.getUse());");
        //HL7GeneralCode is not persistent yet
        this.em.persist(addr.getUse());
        //since there is a cascade ALL on the adresses relationship addr is now persistent
        return;
    }
    System.out.println("=========================== inside jpahl7patientrespository.savehl7Address(addr)");
    if ((Integer)addr.getId() == null) {
        System.out.println("[[[[[[[[[[[[ about to persist address ]]]]]]]]]]]]]]]]]]]]");
        this.em.persist(addr);}
    else {
        System.out.println("]]]]]]]]]]]]]]]]]] about to merge address [[[[[[[[[[[[[[[[[[[[[");
        this.em.merge(addr);}
}

The relevant part of HL7Address is now:

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumns({ @JoinColumn(name = "usecode", referencedColumnName = "code", insertable = false, updatable = false),
        @JoinColumn(name = "usecodesystem", referencedColumnName = "codesystem", insertable = false, updatable = false)
})
public HL7GeneralCode use;

The relevant part of HL7GeneralCode is now:

@OneToMany(mappedBy = "use")
private Set<HL7Address> addresses;

The new stack trace can be read by clicking on this link.

How can I resolve this error?

---

THIRD EDIT:

I followed ben75's advice by adding the following code to the save address method:

if(addr.getUse() != null && !this.em.contains(addr.getUse())){
    System.out.println("about to this.em.persist(addr.getUse());");
    this.em.persist(addr.getUse());return;
}

Unfortunately, I am still getting the same error despite the fact that the stack trace SYSO indicates that the above code is running just before the app crashes.

You can read the resulting stack trace by clicking on this link.

Answer 1

First of all there are some things to clear out:

1. You have a bidirectional association between HL7GeneralCode(the parent) and HL7Address (the child). If the HL7GeneralCode.addresses is the "inverse" side (mappedBy) then why the owning side HL7Address.use has insertable/updatable false? The owning side should control this association so you should remove the insertable/updatable=false flags.

2. It always makes sense to cascade from the Parent to the Child, not the other way around. But in your use case, you try to persist the Child and automatically persist the Parent too. That's why the CASCADE.ALL on the many to one end doesn't make sense.

3. When using bidirectional associations, both sides are mandatory to be set:

   HL7Address addr = new HL7Address();
   HL7GeneralCode code = new HL7GeneralCode();
   ...
   code.getAddresses().add(addr);
   addr.setUse(code); 
   

4. The persist operation is meant to INSERT transient entities, never to merge them or reattach entities. This implies that both the HL7Address and the HL7GeneralCode are new entities when you call your service method. If you have already saved a HL7GeneralCode with the same ID, you will get the primary key constraint violation exception.

5. If the HL7GeneralCode is possible to exist, then you should fetch it from db.

   HL7GeneralCode code = em.find(HL7GeneralCode, pk);
   HL7Address addr = new HL7Address();
   if(code != null) {
      code = new HL7GeneralCode();
      em.persist(code);    
   }
   code.getAddresses().add(addr);
   addr.setUse(code);            
   em.persist(addr);
   

UPDATE

6. The HL7Address address doesn't override equals/hashCode so the default object same reference check rule applies. This will ensure we can add/remove addresses from the code.addresses List. In case you change your mind later, make sure you implement equals and hashCode properly.

7. Although not related to your issue, you might want to use getter/setter instead of making your fields public. This provides better encapsulation and you will avoid mixing setters with public field access.

The savehl7Address method:

@Override
public void savehl7Address(HL7Address addr) {
    HL7GeneralCode code = addr.use();
    if(code != null && code.getId()==null){
    //HL7GeneralCode is not persistent. We don't support that
        throw new IllegalStateException("Cannot persist an adress using a non persistent HL7GeneralCode");
       //In case you'd want to support it
       //code = em.find(HL7GeneralCode, code.getId());
    }
    //Merge the code without any address info        
    //This will ensure we only reattach the code without triggering the address 
    //transitive persistence by reachability
    addr.setUse(null);
    code.getAddresses().remove(addr);
    code = em.merge(code); 

    //Now set the code to the address and vice-versa  
    addr.setUse(code);
    code.getAddresses().add(addr);

    if ((Integer)addr.getId() == null) {
        System.out.println("[[[[[[[[[[[[ about to persist address ]]]]]]]]]]]]]]]]]]]]");
        em.persist(addr);
    }
    else {
        System.out.println("]]]]]]]]]]]]]]]]]] about to merge address [[[[[[[[[[[[[[[[[[[[[");
        addr = em.merge(addr);
    }       
}

Answer 2

It seems that the problem is the CascadeType.ALL on the use relationship.

What's going on ?

• You have one instance of HL7GeneralCode persistent in your db. Let's call it : code1.

• You create a new Address and define the use relation with something like :

  theNewAdress.setUse(code1);

• You call savehl7Address(theNewAddress) and since the address is new you made a call to persist. The problem is that the cascading rule CascadeType.ALL will force a call to persist(code1) and since code1 is already in the db : crash because of duplicate entry.

Solution :

Define no cascading rule for the use relationship :

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumns({ @JoinColumn(name = "usecode", referencedColumnName = "code", insertable = false, updatable = false),
        @JoinColumn(name = "usecodesystem", referencedColumnName = "codesystem", insertable = false, updatable = false)
})
public HL7GeneralCode use;

But you must manage it by hand, especially if there is a use case where the HL7GeneralCode used by the address is not already in the db.

Over-simplified solution (so that you can understand the problem) :

@Override
public void savehl7Address(HL7Address addr) {
    if(addr.use() != null && addr.use().getId()==null){
        //HL7GeneralCode is not persistent yet
        this.em.persist(addr.use());
        //since there is a cascade ALL on the adresses relationship addr is now persistent
        return;
    }
    if ((Integer)addr.getId() == null) {
        System.out.println("[[[[[[[[[[[[ about to persist address ]]]]]]]]]]]]]]]]]]]]");
        this.em.persist(addr);}
    else {
        System.out.println("]]]]]]]]]]]]]]]]]] about to merge address [[[[[[[[[[[[[[[[[[[[[");
        this.em.merge(addr);}
}

As you can see this solution is certainly not the best and not production ready. The real solution is to study all your use-case and adapt the cascading rule (on both use and adresses relationships) accordingly.

In my opinion, the best thing to do is to ensure that the HL7GeneralCode is already persistent when you call savehl7Address and so something like this is probably a better solution:

@Override
public void savehl7Address(HL7Address addr) {
    if(addr.use() != null && addr.use().getId()==null){
        //HL7GeneralCode is not persistent. We don't support that
        throw new IllegalStateException("Cannot persist an adress using a non persistent HL7GeneralCode");
    }
    if ((Integer)addr.getId() == null) {
        System.out.println("[[[[[[[[[[[[ about to persist address ]]]]]]]]]]]]]]]]]]]]");
        this.em.persist(addr);}
    else {
        System.out.println("]]]]]]]]]]]]]]]]]] about to merge address [[[[[[[[[[[[[[[[[[[[[");
        this.em.merge(addr);}
}