How to delete automatically all reference rows if parent row get deleted in mysql?
15,015
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 ;
Comments
-
Aamir about 2 years
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.
-
Aamir over 10 yearsI 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.
-
a_horse_with_no_name over 10 years@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.