InnoDB hierarchical data: recursively delete fragment of a tree

Question

This is my 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 is a unique id (autoincrement) and parent is also an id from the same table. If parent is NULL, the page hasn't got parent.

What I want? If I delete one parent, it should auto delete all his childs in the same table. I believe that this can be done using on DELETE CASCADE, and this is the way that I want to do it :). But I've tried many configurations of code, and none of them work. Either table cannot be created, or insert query is not working, because of an error that looks similar to "key not exist".

What I found?

How to recursively delete items from table? - answer is great, but with none of code. This answer to the same question: https://stackoverflow.com/a/9260373/1125465 doesn't work for me. There are some problems with table creation. I think this answer was made in a hurry, and there is some key word missing?

Recursive MySQL Query with relational innoDB this one is simmilar, but not the same case, there are few tables.

Sql server - recursive delete - Come on, there must be a simpliest answer...

Technical info:

mysql Ver 14.14 Distrib 5.1.70, for pc-linux-gnu (x86_64) using readline 5.1
Table engine: InnoDB

There is no simple answer in MySQL because it lacks the recursive queries (or deferrable constraints) — , Jun 26 '14 at 07:54

score 1 · Accepted Answer · answered Jun 26 '14 at 08:01

This works for me (and here's a 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
INTO    test
VALUES  (1, NULL),
        (2, 1),
        (3, 2),
        (4, 3),
        (5, NULL),
        (6, 5);

SELECT  *
FROM    test;

+----+--------+
| id | parent |
+----+--------+
|  1 |   NULL |
|  5 |   NULL |
|  2 |      1 |
|  3 |      2 |
|  4 |      3 |
|  6 |      5 |
+----+--------+

DELETE
FROM    test
WHERE   id = 1;

SELECT  *
FROM    test;

+----+--------+
| id | parent |
+----+--------+
|  5 |   NULL |
|  6 |      5 |
+----+--------+

Wow, I know my mistake. I was inserting with a procedure, and I was trying to insert a record with parent 0 and not NULL. And then and error appears: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`test`.`pages`, CONSTRAINT `fk_pages_pages` FOREIGN KEY (`parent`) REFERENCES `pages` (`id`) ON DELETE CASCADE). Thx for Your help, +1 accepted. Maybe it will be usefull for others with same problem. Thanks a lot! — Jacek Kowalewski, Jun 26 '14 at 08:10

InnoDB hierarchical data: recursively delete fragment of a tree

1 Answers1