How to delete Category and SubCateogries?

Question

I have little Confusion that when we delete Category is it necessary to delete Subcategory related to that if so please help by writing Sql query?Similarly if delete subcategory then deleting Category will also should be deleted as well?

+-------+------------+
| CatID |  CatName   |
+-------+------------+
|     1 | Seeds      |
|     2 | Pesticides |
|     3 | Fertilizer |
+-------+------------+

+----------+---------------+-----------+
| SubCatID |  SubCatName   |    CatID  |
+----------+---------------+-----------+
|        1 | Red Seed      |         1 |
|        2 | Red Seed      |         1 |
|        3 | Red Seed      |         1 |
|        4 | Red Seed      |         1 |
|        5 | National Worm |         2 |
+----------+---------------+-----------+

What have you already tried? What do you think is a possible answer? Have you researched "foreign key relationships" and "deleting a record"? — Thomas, Oct 31 '18 at 11:53
Possible duplicate of [MySQL foreign key constraints, cascade delete](https://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete) — Thomas, Oct 31 '18 at 11:57
Are you sure you want to delete a category (and leave subcategories orphaned) when a *subcategory* is deleted? Maybe you meant when a category's *last* subcategory is deleted? Even then I don't know if I would make that automatic. You have some suggestions for `CASCADE` but I generally [steer people away from that option](https://stackoverflow.com/questions/3578975/what-are-the-pros-and-cons-of-cascading-delete-and-updates). For a relationship this simple it is quite easy to encapsulate this logic in a single, easy-to-debug stored procedure to handle category and/or subcategory deletes. — Aaron Bertrand, Oct 31 '18 at 12:09
@AaronBertrand sure. But the idea of a cascading delete is pretty universal. — Thomas, Oct 31 '18 at 13:11
@Thomas Doesn't make it a good duplicate. The answers have syntax *this OP can't use*. — Aaron Bertrand, Oct 31 '18 at 13:12
@AaronBertrand Thanks I have applied the cascading logic and its working. — Arslan jappa, Oct 31 '18 at 14:04

Felipe Oriani · Answer 1 · 2018-10-31T11:59:58.160

Given you have a Foreign Key defined on this relation, then yes, you have to delete (because childs cannot be orphans) or set null (if you column CatId allows null values) on the CatID column on the Subcategories table because the relational databases uses the referential integrity. You can define the CASCADE option to do it for you automatically.

ALTER TABLE SubCategory
ADD CONSTRAINT FK_SubCategory_Category
    FOREIGN KEY (CatId)
    REFERENCES Category (CatId)
    ON DELETE CASCADE

Check this article for more details.

If you have a FK and want more control what you are deleting, you must provide two sql statements to make it, for sample.

delete from subcategory where catId = 1
delete from category where catId = 1

score 1 · Answer 2 · answered Oct 31 '18 at 11:53

You need ON DELETE CASCADE by altering current schema :

ALTER TABLE SubCategory
    ADD CONSTRAINT fk_SubCat_CatID FOREIGN KEY (CatID) REFERENCES category(CatID )
    ON DELETE CASCADE;

By this when you delete category from category table, the reference data would auto delete. Just make sure drop your current constraint before create new one with ON DELETE CASCADE.

How to delete Category and SubCateogries?

2 Answers2