I have two entities-
- User
- 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<>();