Доброго времени суток форумчани.

Есть табличка

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(10) NOT NULL AUTO_INCREMENT, /*Идентификатор раздела*/
  `sweat_category` int(10) NOT NULL DEFAULT '0', /*Идентификатор под раздела*/
  PRIMARY KEY (`id`),
  INDEX `ixSweatCategory` (`sweat_category`)
) ENGINE=InnoDB;

Предположим есть категория
категория 1 -> куклы -> Барби -> Что-то ещо;
категория 1 -> Плюшевые игрушки -> Фирма Авалон;
категория 1 -> Роботы;

Как мы видим у категории нет лимита по вложенности и данная задача требует рекурсивных подход.
Задача нужно удалить категорию Куклы и все её пот категории и их поткатегории.
Как можно это реализовать на уровне MySql ?

Самый простой путь - при создании таблицы поставить внешний ключ на поле `sweat_category` с параметром "ON DELETE CASCADE":

CREATE TABLE `category` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `sweat_category` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT FOREIGN KEY (`sweat_category`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

Тогда при удалении записи будут автоматически удаляться и все ее "дочерние" узлы.

Решения готового для вас не могу предложить (кроме очевидных, разумеется).

С одной стороны, можно было бы попробовать положиться на внешние ключи. Можно было бы, если бы они в мускуле работали так, как должны, ан-нет. Их валидация происходит не после выполнения всего запроса, а после удаления каждой отдельной записи. Попробовать, конечно, можно, сам не проверял конкретно этот случай, но с последствиями этого идиотского поведения постоянно приходится иметь дело.

Второе - многотабличный delete. Разумеется, здесь не идет речь о неограниченной глубине, ну а, скажем, запросом типа

delete a, a2, a3, a4
  from tableA a
    left join tableA a2 on a.id = a2.parent_id
    left join tableA a3 on a2.id = a3.parent_id
    left join tableA a4 on a3.id = a4.parent_id
  where a.id = 100

выглядит не очень симпатично, но если
1. глубина может быть ограничена
2. внешние ключи не пашут

то такое решение, пожалуй, будет наиболее коротким и правильным.

PS:
еще есть вариант использовать хранимую процедуру, куда всунуть всю логику по удалению потомков. Получится не шибко красиво, но вся некрасота будет на стороне mysql, если это важно

CREATE TABLE `category` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `sweat_category` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT FOREIGN KEY (`sweat_category`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

delete a, a2, a3, a4
  from tableA a
    left join tableA a2 on a.id = a2.parent_id
    left join tableA a3 on a2.id = a3.parent_id
    left join tableA a4 on a3.id = a4.parent_id
  where a.id = 100

CREATE TABLE `category` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `sweat_category` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT FOREIGN KEY (`sweat_category`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

CREATE TABLE `category2` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `sweat_category` int(10) DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT FOREIGN KEY (`sweat_category`) REFERENCES `category2` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
SET FOREIGN_KEY_CHECKS=0;
INSERT into `gumped_beta`.`category2` (`sweat_category`,`name`) VALUES ('0','Корневая категория');
SET FOREIGN_KEY_CHECKS=1;