在DB中删除CASCADE结构

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:

我想用CASCADE DELETE实现的目的是:

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.

当我删除产品时,它也将从Products2categories表中删除。 (我已经通过将fK与table products.id一起使用到products2categories.product_id来实现这一点。所以这是一个问题解决了。

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.

当我删除一个类别时,我想删除所有的“儿子”,注意:“儿子”类别可以有另一个“儿子”类别,例如在我给的表中,爸爸是奶奶的儿子和孙子和孙子2是爸爸的儿子,这意味着,当我删除奶奶时,它也会删除爸爸,格兰森,孙子2。此外,我希望这也删除所有与这些类别相关的产品。

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.

我很确定PHP需要这样做,但我也想知道FK的用途和位置。

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

注意:如果您需要任何更多详细信息,请随时提出要求。

1 个解决方案

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

这可能不是一个好主意。在任何情况下,您可能都必须为此编写触发器,或者在应用程序代码中执行此操作。产品与“Products2ategories”处于一对多关系的“一”方面。要将“Products2ategories”中的删除级联到“Products”,您需要在“Products”中存储“Products2ategories”中的密钥。当然,这不起作用,因为你可能需要很多这样的键。