Hello. There is a table

CREATE TABLE IF NOT EXISTS `tb_category` ( `id` int(11) NOT NULL AUTO_INCREMENT, `id_parent` int(11) NOT NULL, `eng_title` varchar(255) NOT NULL, `title` varchar(255) NOT NULL, `image` varchar(63) NOT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

I would like to add such a function that if an entry is deleted with id_parent = 0 (that is, the root), then all the child elements were also deleted, I read about ON DELETE CASCADE started to implement, but as I understood there you need to use 2 tables for this, and if I am all in the same table, what to do, because when I made this connection, I could no longer add records even if the table was empty? I have so far from thoughts only use 2 requests.

    2 answers 2

    Similar functionality can be implemented through FOREIGN KEY

    https://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html

    ON DELETE CASCADE - deletes data from the child table if the row from the parent table is deleted.

    example:

     CREATE TABLE parent ( id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB;

    As correctly noted by @retvizan, FOREIGN KEY can be done on the same table.

    example:

     CREATE TABLE just_table ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES just_table(id) ON DELETE CASCADE ) ENGINE=INNODB;

    Tip:

    Do not delete the entries, just flag them as deleted.

    1. Removal is a very expensive procedure. Someone can disable your application simply by adding a large number of children to the parent and then deleting the parent's entry.
    2. In case of errors, you may accidentally get incomplete data.

      misunderstood, optional 2 tables:

       alter table `tb_category` add foreign key (`id`) references `tb_category`(`id_parent`) ON DELETE CASCADE;