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.)
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;
Just my $0.02 - this not so trivial solution should require MVC to handle the cascade deletion.