PHP MySQL Delete parent and child rows

Question

I have 1 MySQL Table. It looks like this:

+---------+-------------+--------+
| item_id |  parent_id  |  Name  |
+---------+-------------+--------+
|   1     |     0       |  Home  |
+---------+-------------+--------+
|   2     |     1       |  Sub   |
+---------+-------------+--------+
|   3     |     2       | SubSub |
+---------+-------------+--------+

If I DELETE item_id 1, I want to delete the rest of the sub also but how can I do it?

I have tried the Foreign Key but it works only if you have 2 tables??

I hope someone can help me in MySQL maybe PHP?

Answer 1

You can, most definitely, use self-referencing foreign keys with MySQL (you don't need multiple tables). However, for any kind of foreign key support, you need to use the InnoDB engine. And my guess is, that you are using the MyISAM engine.

With InnoDB you could create a table, similar to what you have already, including the self-referencing foreign key, like this:

CREATE TABLE  `yourTable` (
  `item_id` int(10) unsigned NOT NULL auto_increment,
  `parent_id` int(10) unsigned default NULL,
  `Name` varchar(50) NOT NULL,
  PRIMARY KEY  (`item_id`),
  KEY `FK_parent_id` (`parent_id`),
  CONSTRAINT `FK_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `yourTable` (`item_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then, when you issue a DELETE statement, like:

DELETE FROM `yourTable` WHERE `item_id` = 1;

... it would delete each 'child' row, that has a parent_id of 1 as well. If any of those 'child' rows have children of their own, they'd be deleted too, etc. (that's what the ON DELETE CASCADE means).