How to use delete cascade in MySQL MyISAM?

Question

I have one table that I called equipment , and 8 other tables that I called equipment_child1 , etc. to equipment_child8 .

The commom field is between all these cod_equip tables, with this field I can identify all my baby equipment tables with the parent table equipment .

I need to delete data from equipment when moving equipment , but I need to delete data in all equipment_child1 tables in equipment_child8.

then I remember I used DELETE CASCADE in the innoDB engine, but now I use MyISAM engina, is this a problem?

Any help really clarify ...

Answer 1

Yes. You just can't with this engine.

to change. You can write a trigger that, after deleting a record in your table, deletes all child records in all other tables.

Ok I wrote you an example:

create table tab1 ( id int ) engine = myisam; insert into tab1 values (1),(2),(3),(4); create table tab2( id int not null auto_increment primary key, id_tab1 int ) engine = myisam; insert into tab2 (id_tab1) values (1),(2),(2),(3),(4); create table tab3( id int not null auto_increment primary key, id_tab1 int ) engine = myisam; insert into tab3 (id_tab1) values (1),(2),(2),(3),(2); delimiter // create trigger deletecascade after delete on tab1 for each row begin delete from tab2 where id_tab1 = old.id; delete from tab3 where id_tab1 = old.id; end; // delimiter ; delete from tab1 where id = 2; 

Hope this helps.

to change. Obviously this works even if you delete another identifier from table 1 at the same time:

 delete from tab1 where id in (2,3,4);