Why is the ON DELETE CASCADE not working & How to delete left over references

This is a question same as ON DELETE CASCADE not working in MySQL question where th开发者_运维知识库e My-ISAM engine is not doing its ON DELETE CASCADE part. But the problem is i have to use MyISAM table type itself and not InnoDB

How can i ensure that i can simulate the ON DELETE CASCADE when ever a row gets deleted in my parent table, so that its updated in many other tables it is referencing it.? Can i write some kind of trigger?

From the fine manual:

For other storage engines, MySQL Server parses and ignores foreign key specifications.

So if you need ON DELETE CASCADE behavior and MyISAM tables at the same time then you'll have to do the CASCADE part by hand with a DELETE trigger or something similar outside the database. Adding a trigger like this should work:

create trigger fake_cascade_delete after delete on table_name
for each row begin
    delete from other_table
    where referencing_column = OLD.id;
end;

That's just off the top of my head and the table and column names are, of course, just for demonstration purposes.

There are ways to implement a foreign-key-like behavior, via more coding, semaphores, triggers etc...

But these solutions are not as reliable as InnoDB foreign-key implementation that guarantees the table integrity.
Meaning if the cascade would not be performed for some reason, the initial delete would not be taken into account either.

You could lock the table before doing the DELETE but there is still a non zero probability that the DELETE is effectively performed on the main table while the cascading emulation is not (for some reason).

Tables duplication pseudo solution:

Depending on your needs, if you do need MyISAM for FTI you could do the following (if space is not a concern)

• have two tables, ti and tm (i for Innodb and m for MyIsam),
• depending on your DB topology, duplicate also some children tables if necessary (that would require also FTI)
• perform all of the updates to ti (and children) first in a transaction
• then perform the tm (and children if any) updates in order to have the FTI updated on tm

This way at least you have a reference table ti (and children) that contains reliable data.
You can then (once a day maybe) check the ti table(s) against the tm one(s) and fix the differences on tm table(s), if any.

tm would only contain the TEXT to be included in the FTI and a reference to the ti table.