MySQL primary and foreign key tables are associated with table data and deleted at the same time

You must delete all the data of the primary table that meets a certain condition and associated sub-tables. In fact, this requirement is very simple. If the sub-Table specified ONDELETECA when creating the foreign key

You must DELETE all the data in the primary table that meets a certain condition and associated sub-tables. In fact, this requirement is very simple. If the sub-Table specifies the on delete ca when creating the foreign key

Condition: p (parent table) does not have on delete cascade c (child table)

Mysql> delete a, B from p a, c B where a. id = B. id;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ('hhl '. 'C', CONSTRAINT 'fk _ P_ID 'foreign key ('id') REFERENCES 'P' ('id '))

Mysql> show profiles;

+ ---------- + ------------ + -------------------------------------------- +

| Query_ID | Duration | Query |

+ ---------- + ------------ + -------------------------------------------- +

| 1 | 0.00875600 | delete a, B from p a, c B where a. id = B. id |

| 2 | 0.01294200 | delete a, B from p a, c B where a. id = B. id |

+ ---------- + ------------ + -------------------------------------------- +

Mysql> show profile for query 2;

+ -------------------------- + ---------- +

| Status | Duration |

+ -------------------------- + ---------- +

| Startling | 0.000314 |

| Check permissions | 0.000026 |

| Check permissions | 0.000014 |

| Check permissions | 0.000009 |

| Check permissions | 0.000010 |

| Init | 1, 0.000033 |

| Opening tables | 0.000082 |

| System lock | 0.000047 |

| Init | 1, 0.000050 |

| Deleting from main table | 0.000016 |

| Optimizing | 0.000019 |

| Statistics | 0.000056 |

| Preparing | 0.000042 |

| Executing | 0.000054 |

| Sendingdata | 0.005026 |

| End| 0.000050 |

| Query end | 0.003456 |

| Closing tables | 0.000143 |

| Freeing items | 0.003430 |

| Logging slow query | 0.000047 |

| Cleaningup | 0.000021 |

+ -------------------------- + ---------- +

21 rows in set (0.00 sec)

We can see that there is no operation to delete the sub-table above.

Mysql> delete a, B from c a, p B where a. id = B. id;

Query OK, 6 rows affected (0.04 sec)

Sub-tables following from are in front. The sub-tables are deleted successfully !!

Mysql> show profile for query 3;

+ -------------------------------- + ---------- +

| Status | Duration |

+ -------------------------------- + ---------- +

| Startling | 0.000307 |

| Check permissions | 0.000019 |

| Check permissions | 0.000017 |

| Check permissions | 0.000009 |

| Check permissions | 0.000010 |

| Init | 1, 0.000021 |

| Opening tables | 0.000091 |

| System lock | 0.000036 |

| Init | 1, 0.000047 |

| Deleting from main table | 0.000016 |

| Optimizing | 0.000125 |

| Statistics | 0.000084 |

| Preparing | 0.000042 |

| Executing | 0.000013 |

| Sendingdata | 0.000572 |

| Deleting from reference tables | 0.000103 |

| End| 0.000015 |

| Waiting for query cache lock | 0.000009 |

| End| 0.000010 |

| Waiting for query cache lock | 0.000008 |

| End| 0.000160 |

| End| 0.000022 |

| Query end | 0.030033 |

| Closing tables | 0.000081 |

| Freeing items | 0.001465 |

| Logging slow query | 0.000052 |

| Cleaningup | 0.000011 |

+ -------------------------------- + ---------- +

27 rows in set (0.00 sec)

Another method is to delete the child table at the outermost layer, delete the child table at the first layer, and delete the parent table at the last layer.