MySQL deleting multiple columns from two table

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, n开发者_如何学JAVAame, 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?

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;

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

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
;

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.