☰

MySQL 外部キー制約、カスケード削除 質問する

tag-icon • tag-icon mysql foreign-keys innodb
MySQL 外部キー制約、カスケード削除 質問する

整合性を維持し、孤立を回避するために外部キーを使用したいと思います (すでに innoDB を使用しています)。

DELETE ON CASCADE を実行する SQL ステートメントを作成するにはどうすればよいですか?

カテゴリを削除する場合、他のカテゴリに関連する製品も削除されないようにするにはどうすればよいですか。

ピボット テーブル「categories_products」は、他の 2 つのテーブル間に多対多の関係を作成します。

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

products
- id
- name
- price

categories_products
- categories_id
- products_id

ベストアンサー1

カスケード削除によって、削除されたカテゴリのメンバーであった製品が削除された場合は、外部キーの設定が不適切です。サンプル テーブルでは、次のテーブル設定になっているはずです。

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;

この方法では、製品またはカテゴリを削除でき、categories_products 内の関連レコードのみが同時に削除されます。カスケードはツリーのさらに上まで移動せず、親の製品/カテゴリ テーブルは削除されません。

例えば

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

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

「赤」カテゴリを削除すると、カテゴリ テーブル内の「赤」エントリのみが削除され、prod/cats の 2 つのエントリ「赤いブーツ」と「赤いコート」も削除されます。

削除はそれ以上連鎖されず、「ブーツ」と「コート」のカテゴリは削除されません。

コメントのフォローアップ:

あなたはまだカスケード削除の仕組みを誤解しています。カスケード削除は、「on delete cascade」が定義されているテーブルにのみ影響します。この場合、カスケードは「categories_products」テーブルに設定されています。「red」カテゴリを削除すると、categories_products でカスケード削除されるレコードは、次のレコードのみですcategory_id = red。「category_id = blue」のレコードには影響しません。また、「products」テーブルには外部キーが定義されていないため、そのテーブルには移動しません。

より具体的な例を以下に示します。

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
+------------+-------------+

カテゴリ 2 (青) を削除するとします。

DELETE FROM categories WHERE (id = 2);

DBMS は、'categories' テーブルを指す外部キーを持つすべてのテーブルを調べ、一致する ID が 2 であるレコードを削除します。 では外部キー関係のみを定義したのでproducts_categories、削除が完了すると次のテーブルが作成されます。

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

テーブルには外部キーが定義されていないproductsため、カスケードは機能しません。そのため、ブーツとミトンは引き続きリストされます。ただし、「青いブーツ」と「青いミトン」はもう存在しません。

おすすめ記事