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

Question

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

Answers 5
Added at 2016-12-30 07:12
Tags
Question

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

Answers to

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

nr: #1 dodano: 2016-12-30 07:12

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
nr: #2 dodano: 2016-12-30 07:12

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.

nr: #3 dodano: 2016-12-30 07:12

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/

nr: #4 dodano: 2016-12-30 07:12
$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;
nr: #5 dodano: 2016-12-30 07:12

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.

Source Show
◀ Wstecz