MySQL contraintes de clés étrangères, effacer en cascade

Si votre suppressions en cascade nuke un produit parce qu'il est membre d'une catégorie qui a été tué, alors vous avez mis vos clés étrangères mal. Compte tenu de vos tables, vous devriez avoir le tableau suivant de l'installation:

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;

De cette façon, vous pouvez supprimer un produit OU d'une catégorie, et seuls les enregistrements associés dans categories_products va mourir à ses côtés. La cascade de ne pas voyager plus loin en haut de l'arborescence et de supprimer le produit parent/catégorie table.

par exemple

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

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

Si vous supprimez le "rouge" de la catégorie, alors que le "rouge" entrée dans la table catégories meurt, ainsi que les deux entrées de prod/chats: 'les bottes rouges" et "tuniques rouges'.

Le supprimer ne sera pas en cascade plus loin et ne prendra pas les "bottes" et "couches " catégories".

commentaire suivi:

vous êtes encore à l'incompréhension comment en cascade supprime travail. Ils concernent seulement les tables dans lesquelles les "on delete cascade" est défini. Dans ce cas, la cascade est situé dans le "categories_products" de la table. Si vous supprimez le "rouge" de la catégorie, seuls les enregistrements qui va effacer en cascade dans categories_products sont ceux où category_id = red. Il ne touche pas tous les enregistrements où 'category_id = blue', et il ne serait pas voyager jusqu'à la table "produits", car il n'y a pas de clé étrangère est définie dans le tableau.

Voici un exemple plus concret:

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

Disons que vous supprimer la catégorie #2 (bleu):

DELETE FROM categories WHERE (id = 2);

le SGBD va regarder toutes les tables qui ont une clé étrangère pointant les "catégories" de la table, et supprimer les enregistrements où l'id correspondant est de 2. Depuis que nous avons défini que la relation de clé étrangère dans products_categories, vous vous retrouvez avec cette table une fois la suppression terminée:

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

Il n'y a pas de clé étrangère est définie dans le products de la table, de sorte que la cascade ne fonctionnera pas là, donc vous avez encore des bottes et des mitaines répertoriés. Il n'y a tout simplement pas de "bleu bottes" et non "bleu mitaines' plus.

Je pense (je ne suis pas certain) que les contraintes de clés étrangères ne la fera pas précisément ce que vous voulez, compte tenu de votre structure de la table. Peut-être la meilleure chose à faire est de définir une procédure stockée qui va supprimer une catégorie de la manière que vous voulez, et ensuite appeler cette procédure chaque fois que vous voulez supprimer une catégorie.

CREATE PROCEDURE `DeleteCategory` (IN category_ID INT)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
BEGIN
DELETE FROM
`products`
WHERE
`id` IN (
SELECT `products_id`
FROM `categories_products`
WHERE `categories_id` = category_ID
)
;
DELETE FROM `categories`
WHERE `id` = category_ID;
END

Vous devez également ajouter le suivant les contraintes de clé étrangère de la table de liaison:

ALTER TABLE `categories_products` ADD
CONSTRAINT `Constr_categoriesproducts_categories_fk`
FOREIGN KEY `categories_fk` (`categories_id`) REFERENCES `categories` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `Constr_categoriesproducts_products_fk`
FOREIGN KEY `products_fk` (`products_id`) REFERENCES `products` (`id`)
ON DELETE CASCADE ON UPDATE CASCADE

La CONTRAINTE clause peut, bien sûr, apparaissent également dans l'instruction CREATE TABLE.

Avoir créé ces objets de schéma, vous pouvez supprimer une catégorie et d'obtenir le comportement que vous voulez par l'émission d' CALL DeleteCategory(category_ID) (où category_ID est la catégorie à supprimer), et il va se comporter de la façon dont vous le souhaitez. Mais il n'est pas question normale DELETE FROM requête, sauf si vous voulez plus de norme de comportement (c'est à dire supprimer de la table de liaison, les products seul).