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 ;