MySQL从两个表中删除多列

Question

我有两个这样的表:

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

父列具有父类别的 ID。因此，当我想从类别中删除食品条目时，我想删除所有子类别和文档。

我该怎么做？

Answer 1

如前所述，您可以使用 FOREIGN KEY CONSTRAINTS来完成这样的任务。下面是 MySQL 的新表结构，支持自动删除文档和子类别:

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;