如何在mysql中删除父行时自动删除所有引用行？ [英] How to delete automatically all reference rows if parent row get deleted in mysql?

问题描述

我有一个包含大约50个表的数据库。

假设我有一个名为parent的表，其中包含id主键和24个大约关于这个父表的子表。

我还没有用过删除级联。
我已经搜索了关于做连接可以在所有子表中执行删除。但加入20-30表？它太多了。

请让我知道是否有任何其他解决方案来删除所有这些子行如果父母被删除。

解决方案

您可以使用 ON DELETE CASCADE 。

  ALTER TABLE childTable 
 ADD CONSTRAINT`FK_key` FOREIGN KEY（`childColumnName`）
 REFERENCES parentTable（`parentColumnName`）ON UPDATE CASCADE ON DELETE CASCADE 
  

或

创建 AFTER DELETE TRIGGER 在父表上。添加子表的DELETE查询。

  DELIMITER $$ 
 
 CREATE 
 TRIGGER`tn_aur_department_master `后删除'tn_parentTable` 
为每行开始
删除从childTable WHERE parentId = old.parentId; 
 END; 
 $$ 
 
 DELIMITER; 
  

I have a database which contains around 50 tables.

Suppose I have a table named parent with id primary key and 24 approx child tables with reference to this parent table.

I haven't used on delete cascade. I have already searched about doing joins can perform delete in all child table. But join on 20-30 tables? Its too much.

Please let me know is there any other solution to delete all this child rows if parent get deleted.

解决方案

You can do with ON DELETE CASCADE.

ALTER TABLE childTable
  ADD CONSTRAINT `FK_key` FOREIGN KEY (`childColumnName`) 
  REFERENCES parentTable(`parentColumnName`) ON UPDATE CASCADE ON DELETE CASCADE

OR

Create AFTER DELETE TRIGGER on parent table. Add DELETE queries of child tables.

DELIMITER $$

CREATE
    TRIGGER `tn_aur_department_master` AFTER DELETE ON `tn_parentTable` 
    FOR EACH ROW BEGIN
        DELETE FROM childTable WHERE parentId = old.parentId;
    END;
$$

DELIMITER ;

这篇关于如何在mysql中删除父行时自动删除所有引用行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！