The table has a foreign key so delete error, here are 2 ways to deal with:

Source: Internet
Author: User

The table has a foreign key so delete error, here are 2 ways to deal with:

(1) Temporary setting of foreign key invalidation

(2) Delete table data for tables involving foreign keys

2, foreign key failure of the treatment scheme

mysql> SET foreign_key_checks = 0; # Temporary set foreign key invalidation

Query OK, 0 rows Affected (0.00 sec)

Mysql>

Mysql> Delete from jbpm4_execution; # Perform a delete operation

Query OK, Affected (0.00 sec)

Mysql>

mysql> SET foreign_key_checks = 1;                                                                                                                                                                                           # Restore foreign key after operation end

Query OK, 0 rows Affected (0.00 sec)

Mysql>

3, delete the foreign key table data out of the scheme

To query all foreign key cases related to table locks, query SQL as follows:

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 ';

Executing a query will see the following foreign key:

E:\u\mysql\problem\pic\02.jpg

Then see the table involving the foreign key has jbpm4_variable, Jbpm4_execution, jbpm4_swimlane three tables, then clear the three table data can be.

Mysql> Delete from jbpm4_variable;

Query OK, 1404 rows affected (0.03 sec)

mysql> Delete Fromjbpm4_swimlane;

Query OK, rows affected (0.03 sec)

Mysql> Delete from jbpm4_execution; # Here the error is because the table itself set a foreign key association, so clear the foreign key field data, you can

ERROR 1451 (23000): Cannot delete or update a parent ROW:A FOREIGN KEY constraint fails (' jbpm_db '. ' Jbpm4_execution ', CON STRAINT ' 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; # All foreign key associated data is cleared, now delete data is OK.

Query OK, 203 rows Affected (0.02 sec)

forcing the primary key index

I let him use the Product_goods PG Force index (PRI) to enforce primary key indexing

the experiment of improving Rand random query in MySQL Tens table data

The WHERE clause is the most efficient, the sample Sql:selectsql_no_cache t1.* from hwdb. ' T_huawei ' t1 WHERE t1. ' Hwid ' >= (Selectfloor (RAND () * (SELECT MAX (T2.hwid) from hwdb. ' T_huawei ' T2))) ORDER Byt1.hwid LIMIT 5;

The table has a foreign key so delete error, here are 2 ways to deal with:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.