問題描述
我想使用外鍵來保持完整性并避免孤兒(我已經(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中定義了外鍵關(guān)系code>,一旦刪除完成,你就會得到這個表:
the DBMS will look at all the tables which have a foreign key pointing at the 'categories' table, and delete the records where the matching id is 2. Since we only defined the foreign key relationship in products_categories
, you end up with this table once the delete completes:
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1 | 1 | // red mittens
| 2 | 1 | // red boots
+------------+-------------+
products
表中沒有定義外鍵,所以級聯(lián)在那里不起作用,所以你仍然列出了靴子和手套.只是不再有藍色靴子"和藍色手套"了.
There's no foreign key defined in the products
table, so the cascade will not work there, so you've still got boots and mittens listed. There's just no 'blue boots' and no 'blue mittens' anymore.
這篇關(guān)于MySQL外鍵約束,級聯(lián)刪除的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網(wǎng)!