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.

Constabulary

So, why not use "on delete cascade"? If the FKs are in place, then the delete won't even be possible without a cascade rule: since it sounds like there is no error, the FK relationships are still not setup correctly, which should be done first (and include the cascade while updating the schema). – Sirmons

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 ;

Mcclimans

I have already said that i have 20 to 30 child tables for that parent table so i cant go for each and alter. Is there any other possible way to do it. Trigger method is also helpful, but again i have to write 20-30 delete statements for that one. – Constabulary

@AamirSohail: so what's the problem of writing the alter table once for the involved tables? Any other solution will be much more work in the long run. – Mutate