4

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?

Improve this question

2 Answers 2

Reset to default
5

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).

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You can do this with a self join on the table and not creating another table.
When I want to insert my data I get this error: #1452 - Cannot add or update a child row: a foreign key constraint fails
@user2180410 When using foreign key constraints: if you insert a row with a non-null parent_id value, a record that has item_id with that same value, must exist beforehand, unless you disable foreign keys, temporarily (which I would advice against, as this defeats their purpose). Foreign key constaints are used to ensure data-integrity.
0

Easier actually than thought:

DELETE FROM table WHERE id = # OR parent_id = #; //where # is the same in both places.

Example:

DELETE FROM table WHERE id = 1 OR parent_id = 1;
Improve this answer

4 Comments

The thing is though, that this will not cascade further down. As you can see from OP's example table, the record with item_id = 2 also has a child row of its own (item_id = 3), that will not be deleted with your solution. If OP is OK with this, this will suffice, but OP should be aware that your solution would create orphan records.
Why did I get a down vote for a good answer with a limited scope?? I thought he meant if the id is 1, delete children with parent of 1. I did not understand he wanted all the way down.
In addition a join could solve this. Making a second table is exceptionally inefficient and not a good solve of the problem.
No one is suggesting OP should create a second table. My suggestion was to replace the existing table with a table using the InnoDB engine, so that OP could utilize foreign keys. And JOINing, as you suggest, won't help either, because it will not cascade further than the amount of JOINs you create.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.