create a table relation on itself in mysql

Question

I have a table like so: ``` CREATE TABLE `jngi_category` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `parent` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ``` and i want, when a parent delete all of it's child delete so. if there is any relations that defines by foreign-key so it do that automatically is good.

Accepted Answer

Add a foreign key constraint on this column `parent` with `ON DELETE CASCADE`:

```
CONSTRAINT `FK_ParentCategory` FOREIGN KEY (`parent`)
REFERENCES jngi_category(id) ON DELETE CASCADE ON UPDATE CASCADE
```

SQL Fiddle Demo

create a table relation on itself in mysql

Solution

Problem