DELETE CASCADE structure in a DB

Question

I've got this DB structure:

Products:

+---------------------------+
|id|name |details  |price|cn|
|__|_____|_________|_____|__|
| 1|pen  |somethi  |100  |10|
| 2|paper|something|30   |11|
+---------------------------+

Categories:

+----------------------------+
|id | name      |parent_id   |
|____________________________|
|1  | granny    | 0          |
|2  | dad       | 1          |
|3  | grandson  | 2          |
|4  | grandson 2|  2         |
+----------------------------+

Products2categories:

+-------------------------------+
| id  | product_id | category_id|
|_______________________________|
| 1   | 1          | 3          |
| 2   | 1          | 2          |
| 3   | 1          | 1          |
+-------------------------------+

As you can see, the table Categories is "nested" it refers to other colums of it-self.

What I'm trying to achieve by using CASCADE DELETE is that:

When I delete a product, it will be removed from the Products2categories table as-well. (I have already done this by using a FK with the table products.id to products2categories.product_id. So this is one problem solved.

The main problem is as following:

• When I DELETE a category, I would like to delete all of it's "sons", NOTE: a "son" category can has another "son" category such as in the tables that I've given, where dad is a son of granny and grandson and grandson2 are sons of dad which means, whenver I will delete granny it will also delete dad,granson,grandson2. Moreover, I want this to ALSO delete ALL of the products that are related to those categories.

I'm pretty sure there is a need of PHP to do that, but I would also like to know what FK's to use, and where.

NOTE: If you need ANY more details, feel free to ask for it.

Answer 1

To start, you just need another foreign key constraint referencing the same table.

create table Categories (
  row_id integer not null, 
  parent_name varchar(15) not null,
  child_id integer not null,
  primary key (row_id),
  foreign key (child_id) references Categories (row_id) on delete cascade
);

create table Products2Categories (
  product_id integer not null,
  category_id integer not null,
  primary key (product_id, category_id),
  foreign key (category_id) references Categories (row_id) on delete cascade
);

-- Added a "none" row to resolve the foreign key reference for "granny".
insert into Categories values (0, 'none', 0);
insert into Categories values (1, 'granny', 0);
insert into Categories values (2, 'dad', 1);
insert into Categories values (3, 'grandson', 2);
insert into Categories values (4, 'grandson', 2);

insert into Products2Categories values (1, 1);
insert into Products2Categories values (1, 2);
insert into Products2Categories values (1, 3);

delete from Categories where parent_name = 'dad';

select * from Categories;
row_id  parent_name  child_id
--
0       none         0
1       granny       0

select * from Products2Categories;
product_id  category_id
--
1           1

Moreover, I want this to ALSO delete ALL of the products that are related to those categories.

That's probably not a good idea. In any case, you'll probably have to write a trigger for that, or do it in application code. Products is on the "one" side of a one-to-many relationship with "Products2Categories". To cascade deletes from "Products2Categories" to "Products", you'd need to store a key from "Products2Categories" in "Products". And, of course, that won't work, because you might need many such keys.