MySQL主外键表关联表数据的同时删除

今天做了实验，要求将满足某个条件的主表和相关联的几个子表的数据全部删除，其实这个要求很简单，如果子表在创建外键的时候指定了ON DELETE CASCADE，则直接从主表中删除相关记录，子表中数据也会一起删除。但是现在的子表外键创建时候没有加此语句，如何来实现呢？条件：p（父表）没有ON DELETE CASCADE c（子表） 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 |+--------------------------+----------+| starting | 0.000314 | | checking permissions | 0.000026 | | checking permissions | 0.000014 | | checking permissions | 0.000009 | | checking permissions | 0.000010 | | init | 0.000033 | | Opening tables | 0.000082 | | System lock | 0.000047 | | init | 0.000050 | | deleting from main table | 0.000016 | | optimizing | 0.000019 | | statistics | 0.000056 | | preparing | 0.000042 | | executing | 0.000054 | | Sending data | 0.005026 | | end | 0.000050 | | query end | 0.003456 | | closing tables | 0.000143 | | freeing items | 0.003430 | | logging slow query | 0.000047 | | cleaning up | 0.000021 | +--------------------------+----------+21 rows in set （0.00 sec）看出上面没有删除子表的操作。mysql> delete a,b from c a,p b where a.id=b.id;Query OK, 6 rows affected （0.04 sec）from 后面子表在前，删除成功！！mysql> show profile for query 3 ;
+--------------------------------+----------+| Status | Duration |+--------------------------------+----------+| starting | 0.000307 | | checking permissions | 0.000019 | | checking permissions | 0.000017 | | checking permissions | 0.000009 | | checking permissions | 0.000010 | | init | 0.000021 | | Opening tables | 0.000091 | | System lock | 0.000036 | | init | 0.000047 | | deleting from main table | 0.000016 | | optimizing | 0.000125 | | statistics | 0.000084 | | preparing | 0.000042 | | executing | 0.000013 | | Sending data | 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 | | cleaning up | 0.000011 | +--------------------------------+----------+27 rows in set （0.00 sec）另一种方式：先删除最外层的子表，一层一层向里删除，最后删除父表。ORA-01688表空间无法扩展Oracle redo损坏的处理相关资讯 MySQL外键

MySQL无法创建外键的原因（08/01/2014 12:11:58）

MySQL外键应用（12/13/2012 11:38:37）

本文评论查看全部评论（0）