如何在mysql中删除父行时自动删除所有引用行? [英] How to delete automatically all reference rows if parent row get deleted in mysql?
问题描述
假设我有一个名为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
或 创建 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. OR Create
这篇关于如何在mysql中删除父行时自动删除所有引用行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
AFTER DELETE TRIGGER
在父表上。添加子表的DELETE查询。
DELIMITER $$
CREATE
TRIGGER`tn_aur_department_master `后删除'tn_parentTable`
为每行开始
删除从childTable WHERE parentId = old.parentId;
END;
$$
DELIMITER;
ALTER TABLE childTable
ADD CONSTRAINT `FK_key` FOREIGN KEY (`childColumnName`)
REFERENCES parentTable(`parentColumnName`) ON UPDATE CASCADE ON DELETE CASCADE
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 ;