sql - How to delete automatically all reference rows if parent row get deleted in mysql?

Question

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.

Answer 1

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 ;