MySQL deleting multiple columns from two table

Question

Hello. I have Two tables like this:

Table categories:
columns: id, name, parent
1, Foods, 0
2, Drinks, 0
3, FastFood, 1
4, Hamburger, 3

Table documents:
columns: id, name, categoryID
1, CheseBurger, 4
2, shop, 3

the parent column has the parent category's id. So When i want to delete Foods entry from categories, i want to delete all child categories and documents.

How can I do this?

Answer 1

I would either use a trigger, or create a more detailed "delete" sproc that would handle it.

Answer 2

Some databases support enforcing referential integrity through foreign keys. I've done it with Oracle, but I'm no mysql expert. It's done as an attribute of the foreign key through the keyword 'CASCADE DELETE'. The database automatically handles it for you.

Here's a quick Oracle example:

ALTER TABLE Things ADD CONSTRAINT FK_Things_Stuff
    FOREIGN KEY (ThingID) REFERENCES Stuff (ThingID)
ON DELETE CASCADE
;

Answer 3

You have 2 choices - in either case I recommend that you create foreign key constraints for your relationships.

Choice 1 is to use ON DELETE CASCADE. I think that this is not a good practice, though, because an unintended delete can have quite surprising consequences.

Choice 2 is to walk the tree and find the records that need to be deleted. You can use a self-join for your categories table to identify all the children in n levels of the hierarchy. This is hte prefered approach, imo.

Other ideas like triggers are just a variation of 2.

Answer 4

As mentioned before, you could use FOREIGN KEY CONSTRAINTS to achieve such a task. Below would be your new table structure for MySQL to support automatically deleting both documents and child categories:

CREATE TABLE categories (
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(30) NOT NULL,
   parent INT(11) UNSIGNED,
   INDEX(parent),
   FOREIGN KEY (parent) REFERENCES categories(id) ON DELETE CASCADE       
) engine=InnoDB;

CREATE TABLE documents (
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(30) NOT NULL,
   categoryID INT(11) UNSIGNED NOT NULL,
   INDEX(categoryID),
   FOREIGN KEY (categoryID) REFERENCES categories(id) ON DELETE CASCADE
) engine=InnoDB;

Answer 5

You'll have to code your way to delete it. I notice that categories table is recursive which means the categories can be nested infinite levels deep and therefore probably the on delete cascade won't work (correct me if this is wrong).

The mysql specific delete syntax (specifying left join in a delete query) might work if a maximum nesting depth is enforced. The query goes something along these lines:

DELETE FROM Category 
LEFT JOIN Document ON Category.CategoryID = Document.CategoryID
/* try adding a left joined alias of category table to delete a sub-category */
WHERE CategoryID = 1234