10

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Aamir
  • 738
  • 2
  • 17
  • 41
  • 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). – user2864740 Jan 23 '14 at 07:05

1 Answers1

20

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 ;
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
  • 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. – Aamir Jan 23 '14 at 07:33
  • 4
    @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. –  Jan 23 '14 at 07:45