Deleting Entities @OneToMany @ManyToOne. Key is still referenced from table

Question

I have two entities - User and Song:

User.class:

@Entity
@Table(name = "users")
public class User {
    
   @Id
   @Column(name = "user_id")
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
    
   @Column(name = "first_name")
   private String firstName;
    
   @Column(name = "last_name")
   private String lastName;
    
   @Column(name = "login",unique = true)
   private String login;
    
   @Column(name = "password")
   private String password;
    
   @Column(name = "token")
   private UUID token;
    
   @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
   @JoinTable(
      name = "users_songs",
      joinColumns = @JoinColumn(
         name = "u_id",
         referencedColumnName = "user_id"
      ),
      inverseJoinColumns = @JoinColumn(
         name = "s_id",
         referencedColumnName = "song_id"
      )
   )
   private List<Song> songs = new ArrayList<>();
}

Song.class:

@Entity
@Table(name = "songs")
public class Song {
        
   @Id
   @Column(name = "song_id")
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;
        
   @Column(name = "song_title")
   private String songTitle;
        
   @Column(name = "composer_name")
   @ElementCollection(fetch = FetchType.EAGER)
   @CollectionTable(name = "composers")
   @OnDelete(action = OnDeleteAction.CASCADE)
   @JoinColumn(name = "composer_id")
   private Set<String> composer;
        
   @Column(name = "author_of_words_name")
   @ElementCollection(fetch = FetchType.EAGER)
   @CollectionTable(name = "author_of_words")
   @OnDelete(action = OnDeleteAction.CASCADE)
   @JoinColumn(name = "authorOfWords_id")
   private Set<String> authorOfWords;
        
   @Column(name = "song_artist")
   private String songArtist;
        
   @Column(name = "song_timing")
   private int songTiming;
        
   @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH})
   @JoinColumn(name = "user_id_key")
   private User user;
        
   @Column(name = "rate_value")
   @ElementCollection(fetch = FetchType.EAGER)
   @CollectionTable(name = "rating")
   @MapKeyColumn(name = "login")
   @OnDelete(action = OnDeleteAction.CASCADE)
   @JoinColumn
   private final Map<String, Integer> rating = new HashMap<>();
        
   @Column
   @ElementCollection(fetch = FetchType.EAGER)
   @CollectionTable(name = "comment")
   @MapKeyColumn(name = "login")
   @OnDelete(action = OnDeleteAction.CASCADE)
   @JoinColumn
   private final Map<String, String> comments = new HashMap<>();
}

I want to delete a Song, but not delete a User.

I tried deleting it like this:

Query query = session.createQuery("DELETE Song s WHERE s.user = :user AND s.songTitle = :songTitle");
query.setParameter("user", user);
query.setParameter("songTitle", songTitle);
query.executeUpdate()

But the error drops:

    Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement...
    Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "songs" violates foreign key constraint "fka7me64vk6jtx81wt2ggbvm6ur" on table "users_songs"
     Key (song_id)=(28) is still referenced from table "users_songs".

I understand that the problem is due to the fact that in the spanning table users_songs, which occurred due to the connection @OneToMany.

I read a similar question in the STO and it says that you need to break the connection. how to delete the record from the join table in hibernate

I executed the following code:

List<Song> collect = user.getSongs().stream().filter(s -> !s.getSongTitle().equals(songTitle)).collect(Collectors.toList());
user.setSongs(collect);
session.update(user);
Query query = session.createQuery("DELETE Song s WHERE s.songTitle = :songTitle");
query.setParameter("songTitle", songTitle);
query.executeUpdate();

This code works, but I don't think it's quite right. Now the question. How do I delete a Song from a user? With the condition that the song should not cascade to delete the user.

Please help me solve this issue correctly. I'd really appreciate it.

SternK · Answer 1 · 2021-04-11T13:45:29.777

You can specify ON DELETE CASCADE for the foreign key constraint fka7me64vk6jtx81wt2ggbvm6ur on the table users_songs like below:

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinTable(
   name = "users_songs",
   joinColumns = @JoinColumn(
      name = "u_id",
      referencedColumnName = "user_id"
   ),
   inverseJoinColumns = @JoinColumn(
      name = "s_id",
      referencedColumnName = "song_id"
   ),
   inverseForeignKey = @ForeignKey(
      name = "users_songs_songs_FK",  // fka7me64vk6jtx81wt2ggbvm6ur renamed to users_songs_songs_FK
      foreignKeyDefinition = "FOREIGN KEY (s_id) REFERENCES songs(song_id) ON DELETE CASCADE"
   )
)
private List<Song> songs = new ArrayList<>();

and after database schema regeneration your initial query should work as expected.

Deleting Entities @OneToMany @ManyToOne. Key is still referenced from table

1 Answers1