如果在mysql中删除父行，如何自动删除所有引用行?

2022-01-20 00:00:00 sql parent-child foreign-key-relationship mysql cascading-deletes

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

I have a database which contains around 50 tables.

假设我有一个名为 parent 的表，其 id 主键和 24 个大约参考此父表的子表.

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

我没有使用删除级联.我已经搜索过关于连接可以在所有子表中执行删除.但是加入 20-30 张桌子?太多了.

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.

推荐答案

您可以使用 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 查询.

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 ;

相关文章