Cannot delete or update a parent row after disable foreign key checks

0
votes

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

1
javamysqljpa

1 Answers

1
votes

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

Second option :

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