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-
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<>();