[原]MySQL ERROR 1451 (23000) 外键异常处理
1、执行delete报错
mysql> delete from JBPM4_EXECUTION;
ERROR 1451 (23000): Cannot delete or updatea parent row: a foreign key constraint fails (`jbpm_db`.`JBPM4_EXECUTION`,CONSTRAINT `FK_EXEC_INSTANCE` FOREIGN KEY (`INSTANCE_`) REFERENCES`JBPM4_EXECUTION` (`DBID_`))
mysql>
表有外键所以delete报错了，这里有2种办法处理：
（1） 临时设置外键失效
（2） 删除表涉及到的外键的表的数据
2、外键失效的处理方案
mysql> SET FOREIGN_KEY_CHECKS = 0; # 临时设置外键失效 Query OK, 0 rows affected (0.00 sec)
mysql> mysql> delete from JBPM4_EXECUTION; #执行删除操作 Query OK, 110 rows affected (0.00 sec)
mysql> mysql> SET FOREIGN_KEY_CHECKS = 1; # 操作结束后恢复外键 Query OK, 0 rows affected (0.00 sec)
mysql>
|
3、删除外键表的数据的出来方案
先查询表锁涉及到的所有外键情况，查询sql如下：
SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'JBPM4_EXECUTION'; |
执行查询会看到有如下外键：
E:\u\mysql\problem\pic\02.jpg
然后看到涉及到外键的表的有 JBPM4_VARIABLE、JBPM4_EXECUTION、JBPM4_SWIMLANE三张表，接下来清除这三张表的数据即可。
mysql> delete from JBPM4_VARIABLE; Query OK, 1404 rows affected (0.03 sec)
mysql> delete fromJBPM4_SWIMLANE; Query OK, 13 rows affected (0.03 sec)
mysql> delete from JBPM4_EXECUTION; # 这里报错是因为表自己给自己设置了外键关联，所以清除外键字段的数据，就可以了 ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`jbpm_db`.`JBPM4_EXECUTION`, CONSTRAINT `FK_EXEC_INSTANCE` FOREIGN KEY (`INSTANCE_`) REFERENCES `JBPM4_EXECUTION` (`DBID_`)) mysql> mysql> update JBPM4_EXECUTION set INSTANCE_=null,PARENT_=null,SUBPROCINST_=null,SUPEREXEC_=null; Query OK, 203 rows affected (0.02 sec) Rows matched: 203 Changed: 203 Warnings: 0
mysql> delete from JBPM4_EXECUTION; # 所有外键关联数据都清除了，现在删除数据可以了。 Query OK, 203 rows affected (0.02 sec)
mysql>
|