-
You can write a trigger to do it.
DELIMITER // CREATE TRIGGER CatDelete AFTER DELETE ON categories FOR EACH ROW BEGIN DELETE FROM categories WHERE parent_id = old.id; END// DELIMITER ;
-
You can
ALTER
your MyISAM tables to InnoDB, and then define foreign key constraints with theON DELETE CASCADE
option.ALTER TABLE categories ENGINE=InnoDB; ALTER TABLE categories ADD CONSTRAINT FOREIGN KEY (parent_id) REFERENCES categories (id) ON DELETE CASCADE;
Re your comment, the first thing I'd check is if you have some orphan categories, that is with parent_id
pointing to a non-existant row. That would prevent you from creating the constraint.
SELECT c1.*
FROM categories c1
LEFT OUTER JOIN categories c2
ON (c1.parent_id = c2.id)
WHERE c2.id IS NULL;