How to implement cascading deletion of hierarchical data in MySQL?

Question

Hi,

I'm working on a project that has categories / subcategories. The database table for this is unique, with the following structure :

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL auto_increment,
  `publish` tinyint(1) NOT NULL default '0',
  `parent_id` int(11) NOT NULL default '0',
  `description` text NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

So, in case the category is a "base" one, the parent_id is zero, and if the category has a parent, it herds the parent id. What I want to know is this : I need to delete everything above and related with a category when choosing that option, a cascade-like deletion, but I only have this table (no foreign keys) .. How do I do that? (without a large amount of queries)

Thanks in advance!

Answer 1

• 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 the ON 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;

Answer 2

Just my $0.02 - this not so trivial solution should require MVC to handle the cascade deletion.