delete parent row in a Self relationShip and one to many with two foreign-key constraints

Question

I can't remove one of these relationships or change the ON DELETE CASCADE constraints. My questions are:

In the Category table, How do I delete one parent row (a row that has children and its children as well) ?
In the Product table, How to allow it to delete a product referencing a parent category?

I have tried an Instead Of DELETE trigger but I get an error saying that I cannot define an instead of trigger for a table having on delete cascade constraint. here is my schema...

enter image description here

EDITS:

when I delete a Owner that has categories, the Owner is deleted and his categories (If none of this category has no subcategories.)

If the Category has children, the deletion of Owner fails because of the reference constraint

If the Category has no children, the deletion succeeds.

Finally I found that when I try to delete a Category that has children, I get an error of the due to the ON DELETE NOT ACTION of a basic self referencing table.

Do I understand you correctly that you want to remove only a parent row and leave its children rows untouched? — Michał Komorowski, Sep 19 '14 at 11:29
No! I want to delete the parent and his children as well! But it seems like deletion doesn't work when the parent has one or more children... — Bellash, Sep 19 '14 at 12:15
What do you mean by that it doesn't work? Did you receive any errors? My second question is which relation has ON DELETE CASCADE constraint, the self relationship? — Michał Komorowski, Sep 19 '14 at 12:18
I have updated my question: when I delete a Owner that has categories, the Owner is deleted and his categories (If none of this category has no subcategories.) If the Category has children, the deletion of Owner fails because of the reference constraint If the Category has no children, the deletion succeeds. Finally I found that when I try to delete a Category that has children, I get an error of the due to the ON DELETE NOT ACTION of a basic self referencing table. — Bellash, Sep 19 '14 at 13:23

Michał Komorowski · Answer 1 · 2014-09-19T13:46:04.673

The problem is to find identifiers of a parent and all its children sub-categories. You can do in in the following way:

with Tree(id) as
(
    select CategoryID from Category where CategoryID = PARENT_CATEGORY_ID
    union all
    SELECT c.CategoryID from Category c join Tree tr on c.parentID= tr.id
)
SELECT *
INTO #ToBeRemoved
FROM Tree

Then you can remove products associated with these categories:

DELETE p from FROM Products p
WHERE EXISTS (select 1 from #ToBeRemoved t where t.id = p.idCategory )

After that you can remove categories:

DELETE c FROM Category c
WHERE EXISTS (select 1 from #ToBeRemoved t where t.id = c.CategoryID )

The last step is the deletion of an owner.

Of course you can put all these queries in a stored procedure.

UPDATE

If you only have an identifier of an owner and you want to remove related categories, sub-categories and products you have to modify above CTE a little bit. The following line:

select CategoryID from Category where CategoryID = PARENT_CATEGORY_ID

Should be replaced by:

select CategoryID from Category where idOwner = OWNER_TO_BE_REMOVED

All other steps are the same: remove products associated with found categories, then remove all found categories and sub-categories, then remove the owner.

Thank you for effort Michal I will give it a try... For instance I have deleted the self-referencing constraint and It works very well — Bellash, Sep 19 '14 at 14:09

score -2 · Answer 2 · answered Sep 19 '14 at 15:19

Why would you have self reference in that Category table? That makes no sense to me. It makes sense it would not let you delete a parent as a child could have another owner and that would violate the self reference. Delete is failing for the right reason. You have a data design problem.

delete parent row in a Self relationShip and one to many with two foreign-key constraints

2 Answers2