Home > database >  How to delete entity in ManyToMany Relationship: Spring Boot and Hibernate
How to delete entity in ManyToMany Relationship: Spring Boot and Hibernate

Time:11-27

I have two entities-

  1. User
  2. Category
    There is a @ManyToMany relationship between the two entities. My code, to create this relationship is-
    1.User.java
@ManyToMany(cascade = { CascadeType.DETACH, CascadeType.MERGE,
                CascadeType.REFRESH }, fetch = FetchType.LAZY) 
                
                                                                                                
@JoinTable(name = "enrolled", 
                joinColumns = @JoinColumn(                              
                                name = "user",
                                referencedColumnName = "userid"),
                
                inverseJoinColumns = @JoinColumn(                               
                                name = "category",
                                referencedColumnName = "category_id"))

        List<Category> enrolledCategories=new ArrayList<>();

2.Category.java

 @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name="category_id")
    private Long categoryId;
    @Column(name="title",nullable = false,unique = true)
    private String title;
    @Column(name="descprition",nullable = false)
    private String description;

Code to delete the category is in CategoryServiceImpl-

 @Override
    public void deleteCategory(String categoryId) {
        Optional<Category> category=this.categoryRepository.findById(Long.parseLong(categoryId));
       if(category.isPresent()){
        this.categoryRepository.deleteById(Long.parseLong(categoryId));
       }
       else
       throw new ResourceNotFoundException("Category", "category id", categoryId);
        
    }

The code creates a table named enrolled- Entity Relationship between Category and User via Many to Many relationship Table entries
Problem- When I try to delete the category using this method, I get the following error-

2022-11-26 16:06:26.137 ERROR 20808 --- [nio-8086-exec-9] o.a.c.c.C.[.[.[/]. 
 [dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with   path [] threw exception [Request processing failed; nested exception is  
 org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement] with root cause

java.sql.SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`assessment_portal`.`enrolled`, CONSTRAINT `FKn8uund92met1kb1iduidshdje` FOREIGN KEY (`category`) REFERENCES `category` (`category_id`))
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117) ~[mysql-connector-j-8.0.31.jar:8.0.31]

Please help me find the correct method to delete the category entity, without deleting the user.

CodePudding user response:

The error is causing because of the category you would like to delete is associated with the user table. So, you can use cascade = CascadeType.REMOVE with @ManyToMany or you can disable foreign check :

SET FOREIGN_KEY_CHECKS=0;  /* disable */ 

SET FOREIGN_KEY_CHECKS=1;  /* enable */ 

CodePudding user response:

Please add below @ManyToMany relationship to your Category Entity.

@ManyToMany(cascade = {PERSIST, DETACH})
@JoinTable(name = "enrolled",
        inverseJoinColumns = @JoinColumn(
                name = "user",
                referencedColumnName = "userid"),
        joinColumns = @JoinColumn(
                name = "category",
                referencedColumnName = "category_id"))
private Set<User> users = new HashSet<>();
  • Related