java - 禁用外键检查后无法删除或更新父行

Question

我想更新一个表中的键，而这个键在其他表中是FK。

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

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

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

Answer 1

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

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