問題描述

我想使用外鍵來保持完整性并避免孤兒(我已經(jīng)使用了 innoDB).

I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB).

我如何制作一個 DELETE ON CASCADE 的 SQL 語句?

How do I make a SQL statment that DELETE ON CASCADE?

如果我刪除一個類別，那么我如何確保它不會刪除與其他類別相關(guān)的產(chǎn)品.

If I delete a category then how do I make sure that it would not delete products that also are related to other categories.

數(shù)據(jù)透視表categories_products"在另外兩個表之間創(chuàng)建多對多關(guān)系.

The pivot table "categories_products" creates a many-to-many relationship between the two other tables.

categories
- id (INT)
- name (VARCHAR 255)

products
- id
- name
- price

categories_products
- categories_id
- products_id

推薦答案

如果您的級聯(lián)刪除了一個產(chǎn)品，因為它是被殺死的類別的成員，那么您設(shè)置的外鍵不正確.鑒于您的示例表，您應(yīng)該具有以下表設(shè)置:

If your cascading deletes nuke a product because it was a member of a category that was killed, then you've set up your foreign keys improperly. Given your example tables, you should have the following table setup:

CREATE TABLE categories (
    id int unsigned not null primary key,
    name VARCHAR(255) default null
)Engine=InnoDB;

CREATE TABLE products (
    id int unsigned not null primary key,
    name VARCHAR(255) default null
)Engine=InnoDB;

CREATE TABLE categories_products (
    category_id int unsigned not null,
    product_id int unsigned not null,
    PRIMARY KEY (category_id, product_id),
    KEY pkey (product_id),
    FOREIGN KEY (category_id) REFERENCES categories (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
       ON DELETE CASCADE
       ON UPDATE CASCADE
)Engine=InnoDB;

這樣，您可以刪除一個產(chǎn)品或一個類別，并且只有categories_products 中的關(guān)聯(lián)記錄會一起消失.級聯(lián)不會在樹上進一步移動并刪除父產(chǎn)品/類別表.

This way, you can delete a product OR a category, and only the associated records in categories_products will die alongside. The cascade won't travel farther up the tree and delete the parent product/category table.

例如

products: boots, mittens, hats, coats
categories: red, green, blue, white, black

prod/cats: red boots, green mittens, red coats, black hats

如果刪除 'red' 類別，則只有類別表中的 'red' 條目以及 prod/cats 的兩個條目:'red boots' 和 'redcoats' 消失.

If you delete the 'red' category, then only the 'red' entry in the categories table dies, as well as the two entries prod/cats: 'red boots' and 'red coats'.

刪除不會進一步級聯(lián)，也不會刪除靴子"和外套"類別.

The delete will not cascade any farther and will not take out the 'boots' and 'coats' categories.

評論跟進:

您仍然誤解了級聯(lián)刪除的工作原理.它們只影響定義了刪除級聯(lián)"的表.在這種情況下，級聯(lián)設(shè)置在categories_products"表中.如果刪除紅色"類別，則categories_products 中唯一會級聯(lián)刪除的記錄是那些category_id = red 的記錄.它不會觸及任何 'category_id = blue' 的記錄，也不會前進到products"表，因為該表中沒有定義外鍵.

you're still misunderstanding how cascaded deletes work. They only affect the tables in which the "on delete cascade" is defined. In this case, the cascade is set in the "categories_products" table. If you delete the 'red' category, the only records that will cascade delete in categories_products are those where category_id = red. It won't touch any records where 'category_id = blue', and it would not travel onwards to the "products" table, because there's no foreign key defined in that table.

這是一個更具體的例子:

Here's a more concrete example:

categories:     products:
+----+------+   +----+---------+
| id | name |   | id | name    |
+----+------+   +----+---------+
| 1  | red  |   | 1  | mittens |
| 2  | blue |   | 2  | boots   |
+---++------+   +----+---------+

products_categories:
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1          | 1           | // red mittens
| 1          | 2           | // blue mittens
| 2          | 1           | // red boots
| 2          | 2           | // blue boots
+------------+-------------+

假設(shè)您刪除了類別 #2(藍色):

Let's say you delete category #2 (blue):

DELETE FROM categories WHERE (id = 2);

DBMS 會查看所有有外鍵指向 'categories' 表的表，并刪除匹配 id 為 2 的記錄.因為我們只在 products_categories，一旦刪除完成，你就會得到這個表:

+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1          | 1           | // red mittens
| 2          | 1           | // red boots
+------------+-------------+