[解決済み] 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

解決方法は？

カスケード削除で商品が削除されたカテゴリのメンバーであったために商品が消滅した場合、外部キーの設定が不適切であることが考えられます。あなたのテーブルの例では、次のテーブルのセットアップがあるはずです。

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

red' カテゴリを削除すると、カテゴリテーブルの 'red' エントリのみが削除され、prod/cats の 'red boots' と 'red coats' という 2 つのエントリも削除されます。

削除はそれ以上カスケードせず、'boots' と 'coats' のカテゴリを削除することはありません。

のコメントフォローです。

カスケード削除の仕組みをまだ誤解しているようですね。カスケード削除は、quot;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 テーブルで、カスケードが機能しないので、ブーツとミトンがリストアップされたままになっています。青いブーツ」と「青いミトン」がないだけです。