java - Constraint Violation when persisting One To Many relation

Question

In a spring mvc application using hibernate and MySQL, I am getting the following constraint violation exception:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:  
Cannot add or update a child row: a foreign key constraint fails  
(`mybd`.`hl7_documententity`, CONSTRAINT `hl7_documententity_ibfk_1`   
FOREIGN KEY (`ptcode`, `ptcodesystem`)    
REFERENCES `hl7_generalcode` (`code`, `codesystem`))

The problem occurs when I try to save a DocumentEntity containing a property of type GeneralCode, both of which are defined below.

I have read many postings and blogs on this error, but none seem to resolve my problem. How can I resolve this error?

Here is the DocumentEntity class:

@Entity
@Table(name = "hl7_documententity")
public class HL7DocumentEntity extends BaseEntity{

    //other properties

    @ManyToOne
    @JoinColumns({ @JoinColumn(name = "ptcode", referencedColumnName = "code"),
        @JoinColumn(name = "ptcodesystem", referencedColumnName = "codesystem")
    })
    private HL7GeneralCode providertype;

    //getters and setters    
}

Here is the GeneralCode class:

@Entity
@Table(name = "hl7_generalcodes")
public class HL7GeneralCode implements Serializable{

    private static final long serialVersionUID = -8620565054475096516L;

    @EmbeddedId
    private HL7EmbedCodePK codePk;

    @OneToMany(mappedBy = "providertype")
    private Set<HL7DocumentEntity> documententities;

    ////////////getters and setters    
}

Here is the code from the controller:

HL7GeneralCode authcode = processGeneralCode(grandkid);
HL7GeneralCode testcode = this.clinicService.findGeneralCodeByPK(authcode.getCodePk().getCode(), authcode.getCodePk().getCodesystem());
if(testcode==null){
    authcode.addDocumententity(mydent);
    this.clinicService.savehl7GeneralCode(authcode);
    mydent.setProvidertype(authcode);
    //this next line throws the error
    this.clinicService.savehl7DocumentEntity(mydent);
}else{
    //other stuff
}

Here is the dao method:

@Repository
public class JpaSomethingRepositoryImpl implements SomethingRepository {

    @PersistenceContext
    private EntityManager em;

    @Override
    @Transactional
    public void savehl7DocumentEntity(HL7DocumentEntity de) {
        HL7GeneralCode code = de.getProvidertype();
        if(code !=null && code.getCodePk()==null){//HL7GeneralCode is not persistent. We don't support that
            throw new IllegalStateException("Cannot persist an adress using a non persistent HL7GeneralCode");
        }
        System.out.println("=========================== inside jpaCdaRespository.saveDocEntity(de)");
        de.setProvidertype(null);
        if(code.getDocumententities()!=null){
            ArrayList<HL7DocumentEntity> addrList = new ArrayList<HL7DocumentEntity>();
            addrList.addAll(code.getDocumententities());
            addrList.remove(de);
            Set<HL7DocumentEntity> myaddrs = new HashSet<HL7DocumentEntity>(addrList);
            code.setDocumententities(myaddrs);
        }
        code = em.merge(code); 
        de.setProvidertype(code);
        code.addDocumententity(de);

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

The executed SQL statement and the actual values that hibernate is trying to insert via the sql are:

[[[[[[[[[[[[ about to persist de ]]]]]]]]]]]]]]]]]]]]
DEBUG SQL - insert into hl7_documententity (author_id, authpar_id, entitytype, id_extension, id_root, ptcode, ptcodesystem, id) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into hl7_documententity (author_id, authpar_id, entitytype, id_extension, id_root, ptcode, ptcodesystem, id) values (?, ?, ?, ?, ?, ?, ?, ?)
TRACE BasicBinder - binding parameter [1] as [INTEGER] - <null>
TRACE BasicBinder - binding parameter [2] as [INTEGER] - <null>
TRACE BasicBinder - binding parameter [3] as [VARCHAR] - <null>
TRACE BasicBinder - binding parameter [4] as [VARCHAR] - NI
TRACE BasicBinder - binding parameter [5] as [VARCHAR] - nullFlavor
TRACE BasicBinder - binding parameter [6] as [VARCHAR] - UNK
TRACE BasicBinder - binding parameter [7] as [VARCHAR] - HL7NullFlavor
TRACE BasicBinder - binding parameter [8] as [INTEGER] - 32787
WARN  SqlExceptionHelper - SQL Error: 1452, SQLState: 23000
ERROR SqlExceptionHelper - Cannot add or update a child row: a foreign key constraint fails (`docbd`.`hl7_documententity`, CONSTRAINT `hl7_documententity_ibfk_1` FOREIGN KEY (`ptcode`, `ptcodesystem`) REFERENCES `hl7_generalcode` (`code`, `codesystem`))
INFO  AbstractBatchImpl - HHH000010: On release of batch it still contained JDBC statements
WARN  warn - Handler execution resulted in exception

You can read the EmbedCodePK class code by clicking on this link.
You can read the entire stack trace by clicking on this link.
Here is a link to the code for the BaseEntity class.

Answer 1

Change this:

@OneToMany(mappedBy = "providertype")
private Set<HL7DocumentEntity> documententities;

To this:

@OneToMany(fetch = FetchType.LAZY)
@JoinTable(name = "Link_Documents", joinColumns = {@JoinColumn(name = "codePk", unique = true)}, inverseJoinColumns = {@JoinColumn(name = "change_this_with_primary_key_variable_name_from_HL7DocumentEntity")})
 private Set<HL7DocumentEntity> documententities;

And in HL7DocumentEntity change as follows:

This

@ManyToOne
    @JoinColumns({ @JoinColumn(name = "ptcode", referencedColumnName = "code"),
        @JoinColumn(name = "ptcodesystem", referencedColumnName = "codesystem")
    })
    private HL7GeneralCode providertype;

Change to this:

@ManyToOne(fetch = FetchType.LAZY)
  @JoinTable(name = "Link_Documents", joinColumns = {@JoinColumn(name = "change_this_with_primary_key_variable_name_from_HL7DocumentEntity")}, inverseJoinColumns = {@JoinColumn(name = "codePk")})
  private HL7GeneralCode providertype;

I think you have to change "change_this_with_primary_key_variable_name_from_HL7DocumentEntity" with "id" like it is in BaseEntity but take a look at your sql table, you willsee there the correct name.

I hope you notice How I told JPA to use the same "Link_Documents" table for linking the 2 tables. I think this is were your mistake is. Just make sure to change where I told you with the correct variable name and I think it should work