Mybatis Spring Transactional multiple delete constraint violated

Question

I am working with Spring 3 and Mybatis 3.

Everything is working ok just when i want to make a cascade delete.

Ive got 2 tables with a middle M-M relationship table. Something like Table1 ---> MiddleTable ---> Table2

I want to make a deletion from the midle table and after that delete de data related in the Table2.

In using a Transactional method

@Transactional
public void relacionaReservaLibreBonoLibre(ParametrosRelacionReservaBono params) throws Exception{
    ReservaBean r=rm.buscarReservaPorPK(params.getReserva());


    for(BonoJson b:params.getListaBonosAdd()){
        HotelBean h=hm.buscaHotelPorCodHotel(b.getHotel());
        EstacionBean e=em.buscaEstacionPorEstacionYHotel(b.getEstacion(),h.getCnHotel());

        DocumentoBean db=new DocumentoBean();
        db.setCnEstacion(e.getCnEstacion());
        db.setCnHotel(h.getCnHotel());
        db.setCnTipDoc(r.getCnTipoDoc());
        db.setFlLibre(true);
        db.setTeDoc(b.getCodBono());
        Integer docId=dm.insertaDocumento(db);

        DocumentoReservaBean drb=new DocumentoReservaBean();
        drb.setCnDoc(docId);
        drb.setCnReserva(r.getCnReserva());

        drm.insertaDocumentoReserva(drb);
    }

    for(BonoJson b:params.getListaBonosQuit()){
        HotelBean h=hm.buscaHotelPorCodHotel(b.getHotel());
        EstacionBean e=em.buscaEstacionPorEstacionYHotel(b.getEstacion(),h.getCnHotel());


        ReservaDocumentoReservaBean filtro=new ReservaDocumentoReservaBean();
        filtro.setTeDoc(b.getCodBono());
        filtro.setCnReserva(r.getCnReserva());
        filtro.setFlLibre(true);
        List<ReservaDocumentoReservaBean> resPrev=rdm.getReservaDocumentos(filtro);

        for(ReservaDocumentoReservaBean resPart:resPrev){


            DocumentoReservaBean drb=new DocumentoReservaBean();
            drb.setCnDocReserva(resPart.getCnDocReserva());
            drm.eliminaDocumentoReservaPorPK(drb);

            DocumentoBean db=new DocumentoBean();
            db.setCnDoc(resPart.getCnDoc());
            dm.eliminaDocumentoPorPK(db);
        }
    }

}

It works great just when is executes de

dm.eliminaDocumentoPorPK(db);

It launches the Constraint violation Table2 to Middle table, that its suposed to be deleted in

drm.eliminaDocumentoReservaPorPK(drb);

¿Any hint?

Thanks in advance.

Answer 1

There are several options:

1. Delete from Table2 and then delete from MiddleTable
2. If this is acceptable (that is MiddleTable entity owns Table2 entity) then change foreign key in database so that rows in Table2 were deleted by cascade when row in MiddleTable is deleted. Just add ON CASCADE DELETE to foreign key from Table2 to MiddleTable definition.
3. Make foreign key constrain deferred if your database supports this.