无法删除或更新父行 ConstraintViolationException

2022-01-20 00:00:00 foreign-key-relationship spring mysql spring-mvc Hibernate

我有 2 个对象实体(用户和电话)，它们应该具有多对多关系.

I have 2 object entities (User and Phone) and they are supposed to have many-to-many relations.

User.java

//all columns

@ManyToMany(cascade = CascadeType.MERGE, fetch = FetchType.EAGER)
    @JoinTable(name = "USER_PHONE",
            joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "phone_id", referencedColumnName = "id"))
    private List<Phone> phones;

Phone.java

//all columns
@ManyToMany(cascade = CascadeType.MERGE, fetch = FetchType.EAGER)
    @JoinTable(name = "USER_PHONE",
            joinColumns = @JoinColumn(name = "phone_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"))
    private List<User> userList;

现在，我在我的 USER 表中添加了 2 个 ID 分别为 1 和 2 的用户.然后，我添加一个 id 为 1 的电话并将它们映射到两个用户 IDs(1&2) .

Now, I add 2 users with IDs 1 and 2 in my USER table. Then, I add a single phone with id 1 and map them to both the user IDs(1&2) .

我的 USER_PHONE 表如下所示:

My USER_PHONE table looks as below:

Select * from USER_PHONE;
+----------+---------+
| phone_id | user_id |
+----------+---------+
|        1 |       1 |
|        1 |       2 |
+----------+---------+

现在，我希望删除 ID 为 2 的用户.当我尝试这样做时，我得到一个错误

Now, I wish to remove a user with ID 2. When I try to do this, I get an error 

javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`dbname`.`USER_PHONE`, CONSTRAINT `FKC6A847DAFA96A429` FOREIGN KEY (`user_id`) REFERENCES `USER` (`ID`))

我的删除脚本:

  String query = "DELETE User where id=?1";
        try{
            Query q = entityManager.createQuery(query);
            q.setParameter(1,id);
            q.executeUpdate();
            System.out.println(System.currentTimeMillis() + " DELETE: userId " + id + " ==> deleted");
        } catch(Exception e){
            e.printStackTrace();
            return false;
        }

知道我哪里出错了吗?非常感谢:)

Any idea where am I going wrong ? Thanks a lot :)

推荐答案

尝试使用 entityManager.createNativeQuery().您不能使用 createQuery()，因为该表应该作为一个实体出现在您的 Java 代码中.此外，您需要使用准确的 SQL 格式.

Try using entityManager.createNativeQuery(). You cannot use createQuery() because the table should be present as an entity in your Java code. Also, you need to use the exact SQL format.

String query = "DELETE FROM USER_PHONE WHERE user_id=?1";

    try{
        Query q = entityManager.createNativeQuery(query);
        q.setParameter(1,id);
        q.executeUpdate();
        System.out.println(System.currentTimeMillis() + " DELETE User_Phone: userId " + id + " ==> deleted");
    } catch(Exception e){
        e.printStackTrace();
        return false;
    }`

首先从 USER_PHONE 中删除该行(使用 createNativeQuery())，然后从 User 中删除该行(使用 createQuery())

First delete the row from USER_PHONE (using createNativeQuery()), and then from User (using createQuery())

相关文章