mysql - InnoDB hierarchical data: recursively delete fragment of a tree -

this table:

create table `pages` (     `id` int(11) not null auto_increment,     `parent` int(11) default null,     `label` varchar(255) not null,     primary key (`id`) ) engine=innodb default charset=utf8; 

where id unique id (autoincrement) , parent id same table. if parent null, page hasn't got parent.

what want? if delete 1 parent, should auto delete childs in same table. i believe can done using on delete cascade, , way want :). i've tried many configurations of code, , none of them work. either table cannot created, or insert query not working, because of error looks similar "key not exist".

---

what found?

how recursively delete items table? - answer great, none of code. answer same question: https://stackoverflow.com/a/9260373/1125465 doesn't work me. there problems table creation. think answer made in hurry, , there key word missing?

recursive mysql query relational innodb 1 simmilar, not same case, there few tables.

sql server - recursive delete - come on, there must simpliest answer...

---

technical info:

• mysql ver 14.14 distrib 5.1.70, pc-linux-gnu (x86_64) using readline 5.1
• table engine: innodb

this works me (and here's fiddle http://sqlfiddle.com/#!8/d15b4/1):

create table         test         (         id int not null primary key,         parent int,         constraint                 fk_test_test         foreign key                 (parent)         references                 test (id)         on delete cascade         );  insert    test values  (1, null),         (2, 1),         (3, 2),         (4, 3),         (5, null),         (6, 5);  select  *    test;  +----+--------+ | id | parent | +----+--------+ |  1 |   null | |  5 |   null | |  2 |      1 | |  3 |      2 | |  4 |      3 | |  6 |      5 | +----+--------+  delete    test   id = 1;  select  *    test;  +----+--------+ | id | parent | +----+--------+ |  5 |   null | |  6 |      5 | +----+--------+