MySQL error 1451 23000 foreign key exception handling

Share my teacher’s artificial intelligence tutorial! Zero basis, easy to understand! http://blog.csdn.net/jiangjunshow

You are also welcome to reprint this article. Share knowledge, benefit the people, and realize the great rejuvenation of the Chinese nation!

               
 
 
 
1. Execute DELETE to report an error
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>
 
 
The table has a foreign key, so delete is wrong. Here are two ways to handle it:
(1) Temporary setting of foreign key failure
(2) Delete the table data of the foreign key involved in the table
 
 
 
2. Handling scheme of foreign key failure

mysql> SET FOREIGN_KEY_CHECKS = 0; # Temporarily set foreign key invalidation Query OK, 0 Rows affected (0.00 SEC) MySQL> Mysql> delete from JBPM4_EXECUTION; # Execute delete operation Query OK, 110 Rows Affected (0.00 SEC) MySQL> Mysql> SET FOREIGN_KEY_CHECKS = 1; After the # operation restore foreign keys Query OK, 0 Rows affected (0.00 SEC) MySQL>
 
 
3, delete the foreign key table data out of the scheme
 
First, query all the foreign key situations involved in table lock, and query THE SQL as follows:
The

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME the FROM Information_schema.key_column_usage WHERE REFERENCED_TABLE_NAME = ‘JBPM4_EXECUTION’; usage WHERE REFERENCED_TABLE_NAME = ‘JBPM4_EXECUTION’;

Executing the query will see the following foreign key:
E:\u\mysql\problem\pic\02.jpg

 
Then you see the tables that involve foreign keys are JBPM4_VARIABLE, JBPM4_EXECUTION, and JBPM4_SWIMLANE three tables, and then you can clear the data for these three tables.
 
The

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> The delete from JBPM4_EXECUTION; ERROR 1451 (23000): Cannot delete or update a parent Row: Cannot delete or update a parent Row: Fails (‘ jbPM_DB ‘. ‘JBPM4_EXECUTION’, constraint ‘FK_EXEC_INSTANCE’ foreign key (‘ INSTANCE_ ‘) REFERENCES ‘JBPM4_EXECUTION’ (‘ DBID_ ‘)) mySQL> Mysql> The 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> The delete from JBPM4_EXECUTION; All foreign key associated data has been cleared. Now delete the data. Query OK, 203 rows affected (0.02 SEC) MySQL>
            

Call my teacher’s artificial intelligence tutorial! http://blog.csdn.net/jiangjunshow

Read More: