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:
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.
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.