禁用外键检查后无法删除或更新父行 [英] Cannot delete or update a parent row after disable foreign key checks

问题描述

我想更新一个表中的密钥，而在其他表中此密钥为FK.

I want to update the key in a table and this key is FK in other tables.

em.createNativeQuery("SET FOREIGN_KEY_CHECKS=0").getResultList().

em.createNativeQuery("update user set name = ?1  where name = ?2").executeUpdate();

query.setParameter(1, "aa");
query.setParameter(2, "bb");

但是，即使禁用了外键检查，我也会遇到异常.

However i get an exception even if I disabled the foreign key checks.

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: 无法删除或更新父行:外键约束失败 (test_import_1.assessment_user，约束 FK32kg3rlqty5i2i729a22icn7q外键(user_name)参考 user(name))

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (test_import_1.assessment_user, CONSTRAINT FK32kg3rlqty5i2i729a22icn7q FOREIGN KEY (user_name) REFERENCES user (name))

推荐答案

如何将两个查询作为一个查询使用，如:

What about using both queries as one query like :

StringBuilder query = new StringBuilder();
query.append("BEGIN ")
    .append("SET FOREIGN_KEY_CHECKS = 0 ;")//query to disable foreign key checks
    .append("UPDATE user SET name = ?1 WHERE name = ?2;")//The update query
    .append("SET FOREIGN_KEY_CHECKS = 1")//when you finish enable the check 
    .append(" END;");

Query q = em.createNativeQuery(query.toString());
query.setParameter(1, "aa");
query.setParameter(2, "bb");
q.executeUpdate();// Note executeUpdate() should be used AFTER you set the parameters 

第二个选项:

em.createNativeQuery("SET FOREIGN_KEY_CHECKS = 0").executeUpdate();

Query q = em.createNativeQuery("UPDATE user SET name = ?1 WHERE name = ?2;");
query.setParameter(1, "aa");
query.setParameter(2, "bb");
q.executeUpdate();

em.createNativeQuery("SET FOREIGN_KEY_CHECKS = 1").executeUpdate();

这篇关于禁用外键检查后无法删除或更新父行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！