CODEWITHSUNDEEP
javahibernate
Wojciech Abacki
Wojciech Abacki

Reputation: 251

Delete row with constraint

I want to remove row from entity Reservation. This entity looks like

@Entity
@Table(name="reservation")
public class Reservation {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(nullable = false, unique = true)
    private Integer id;

    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @OnDelete(action = OnDeleteAction.CASCADE)
    @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
    private Seance reservationSeance;

    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinColumn(name = "user_id")
    private User userReservation;

    @OneToMany(orphanRemoval = true)
    private List<Seat> seats = new ArrayList<>();
    ............

}

and my Repertoire 

@Entity
@Table(name="repertoire")
@Data
public class Repertoire {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(nullable = false, unique = true)
    private Integer id;

    @ManyToMany(cascade = {CascadeType.ALL})
    @JoinTable(
            name = "repertoire_seance",
            joinColumns = { @JoinColumn(name = "repertoire_id")},
            inverseJoinColumns = {@JoinColumn(name = "seance_id")}
    )
    List<Seance> seances = new ArrayList<>();
   ......

}

Now i have error 

Cannot delete or update a parent row: a foreign key constraint fails (`todo`.`repertoire_seance`, CONSTRAINT `FKani6veaxbp3vxruqjivg9o9sp` FOREIGN KEY (`seance_id`) REFERENCES `seance` (`id`))

I have in entity relation ManyToMany and hibernate create additional table repertoire_seance.

How to avoid delete records from table Seance, I want only delete records from Reservartion table?

Upvotes: 0

Views: 74

Answers (1)

zuckermanori
zuckermanori

Reputation: 1755

You haven't posted the Seance and repository code so i'll fill up the gaps.

First, you need to remove the @OnDelete(action = OnDeleteAction.CASCADE) annotation as what it practically does is telling the database to delete Seancewhen a Reservation is deleted.

Then, you need to allow null values in your foreign key column (@JoinColumn) of the Reservartion table as follows:

@Entity
@Table(name="Seance")
public class Seance {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(nullable = false, unique = true)
    private Integer id;

    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "reservation_id", nullable = true)
    private Reservation reservation;

    ...
}

And finally, in your DAO you need to set null value for the Seance related to the Reservation you're about to delete and only then delete it, as follows:

public void deleteReservation(int reservationId) {
    Session currentSession = sessionFactory.getCurrentSession();

    // get reservation with primary key
    Reservation reservation = currentSession.get(Reservation.class, reservationId);  
    Seance seance = reservation.getSeance();

    //set reservation_id null
    Seance.setReservation(null);

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

Upvotes: 1

Related Questions