How to delete parent without deleting children in one to many relationship

Question

I am trying to delete a Patient entity without deleting the associated OutboundMessage entities as i want to keep them in the database for reporting/historical purposes. This is the relevant code :

Patient entity

@OneToMany (mappedBy="patient", fetch = FetchType.EAGER,orphanRemoval = false)
public Set<OutboundMessage> getOutboundMessages() 
{
    return outboundMessages;
}

OutboundMessage entity

@ManyToOne (fetch=FetchType.EAGER)
@JoinColumn(name = "id_patient")
public Patient getPatient() 
{
    return patient;
}

When i set a cascade type on the Patient side the records are deleted which is not what i want. When trying as shown in the code above (without the cascade type) I get the following exception :

The DELETE statement conflicted with the REFERENCE constraint "FKqjpga9w6wp3qk26ox9pg252d9". The conflict occurred in database "MDHIS", table "dbo.tblOutboundMessage", column 'id_patient'.

What settings does the owning entity need to allow deletion without cascading to children entities and without cleaning orphaned records?

Thanks!

Answer 1

You need to allow null values in your foreign key column (@JoinColumn) as follows:

@ManyToOne (fetch=FetchType.EAGER)
@JoinColumn(name = "id_patient", nullable = true)
public Patient getPatient() {
    return patient;
}

Then in your DAO you need to set null values for all the OutboundMessage related to the Patient you're about to delete and only then delete it, as follows:

public void deletePatient(int patientId) {
    Session currentSession = sessionFactory.getCurrentSession();

    // get patient with primary key
    Patient patient = currentSession.get(Patient.class, patientId);  
    Set<OutboundMessage> messages = patient.getOutboundMessages();

    //set patient id null
    for(OutboundMessage message : messages) {
        message.setPatient(null);
    }

    //delete the patient
    currentSession.remove(patient);
}