I want to delete all subcategory from database automatically when I delete the parent category

3
votes

When a parent category(e.g., Architecture, Art and Culture) is dropped from the database, I want all subcategories under the parent category to be dropped from the database. I don't know how to write a query for this.

I am using PHP and MySQL database, the category and subcategory are two separate tables, the snapshots of the tables are given below.

Category Table

enter image description here

Subcategory Table

enter image description here

5
phpmysql
Next time post your tables as text instead of images by using the 'SHOW CREATE TABLE` command. – e4c5
Please keep in mind that if you will have children, items, call it what you want them, in these categories or subcategories you will have zombi items, ie unassociated items. One option is to delete these as well or migrate the items to another category. Regarding the delete you may join them as well. DELETE c, s FROM category AS c JOIN subcategory AS s ON c.cat_id = s.parent_id. Another tip I could add is merge the two tables cat_id, parent_id, cat_name, but will require some joining sql – Mujnoi Gyula Tamas
PS: if you will have items in the subcategories then you will have a hard time associating it with the parent category. This is the reason why I suggested to merge the 2 tables – Mujnoi Gyula Tamas

5 Answers

6
votes

Add a foreign key with ON DELETE CASCADE

ALTER TABLE subcategory ADD FOREIGN KEY fk_parent(parent_id)
REFERENCES category(cat_id) ON DELETE CASCADE

This automatically deletes subcategories when the parent is deleted. Please refer to https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html for more information.

0
votes

The trick is not in the query; your sql should be a normal DELETE query.

What you need to do is configure the FOREIGN KEY to ON DELETE CASCADE.

See this tutorial: http://www.mysqltutorial.org/mysql-on-delete-cascade/

0
votes
$id = 1;
DELETE FROM category WHERE cat_id = $id;
DELETE FROM subcategory WHERE parent_id = $id;

if added DELETE CASCADE

$id = 1;
DELETE FROM category WHERE cat_id = $id;
0
votes

You have to set a relation between category and subcategory. I mean category has many subcategories. So it should be one-to-many relationships. And when you will set this relation you have to use onDelete cascade. So once the category will delete then the subcategory will delete automatic.

0
votes

You have to use foreign key here. so if you delete parent id then it child will be delete automatically 

ALTER TABLE subcategory ADD FOREIGN KEY fk_parent(parent_id)
REFERENCES category(cat_id) ON DELETE CASCADE