1

This is a question same as ON DELETE CASCADE not working in MySQL question where the 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?

Community
  • 1
  • 1
footy
  • 5,803
  • 13
  • 48
  • 96

2 Answers2

4

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • this includes even for `ON UPDATE CASCADE`? – footy Aug 14 '11 at 05:30
  • All foreign-key based operations DO NOT WORK with MyISAM tables. – Marc B Aug 14 '11 at 05:31
  • @footy: "For other storage engines, MySQL Server parses and ignores foreign key specifications." The server will parse them and then completely ignore them, they have no effect whatsoever. – mu is too short Aug 14 '11 at 05:33
  • I am doing Heavy Read operations for large tables (.5million rows) while Write operations are very limited to server side admin modifications. My Choice of MyIsam is right correct? Even if i have to simulate the`ON DELETE` operation. – footy Aug 14 '11 at 05:35
  • @footy: If you need full text indexes then you have to use MyISAM. If you could move the full text stuff elsewhere, you'd want to benchmark MyISAM versus InnoDB to see which worked best. You could also check out PostgreSQL but I'm not familiar with PostgreSQL's full text search extension so I can't comment on that. – mu is too short Aug 14 '11 at 05:41
  • @footy: it depends on what is most important to you. If the cascade delete is crucial, use InnoDB; if the performance of MyIsam is sufficiently crucial, then use MyIsam but accept that you have to do the extra coding. – Jonathan Leffler Aug 14 '11 at 05:43
  • @Jonathan: footy also mentioned in the question comments that he needs full text search and MySQL only does that for MyISAM. – mu is too short Aug 14 '11 at 05:50
  • @mu is too short: so the decision must be to use MyIsam and implement the ON DELETE CASCADE constraints by hand. Or maybe it is time to implement the ODC constraints for MyIsam too ... it is one of the benefits of open source. – Jonathan Leffler Aug 14 '11 at 06:45
2

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.

Déjà vu
  • 28,223
  • 6
  • 72
  • 100