Restricciones de clave externa de MySQL, eliminación en cascada

Si su eliminación en cascada destruye un producto porque era miembro de una categoría que fue eliminada, entonces ha configurado sus claves externas incorrectamente. Dadas sus tablas de ejemplo, debería tener la siguiente configuración de tabla:

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 esta manera, puede eliminar un producto O una categoría, y solo los registros asociados en categorías_productos desaparecerán al mismo tiempo. La cascada no subirá más en el árbol y eliminará la tabla principal de producto/categoría.

p.ej

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

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

Si elimina la categoría 'roja', entonces solo muere la entrada 'roja' en la tabla de categorías, así como las dos entradas prod/cats: 'botas rojas' y 'abrigos rojos'.

La eliminación no seguirá en cascada y no eliminará las categorías "botas" y "abrigos".

seguimiento de comentarios:

todavía no entiendes cómo funcionan las eliminaciones en cascada. Sólo afectan a las tablas en las que está definida la "cascada al eliminar". En este caso, la cascada se establece en la tabla "categorías_productos". Si elimina la categoría 'roja', los únicos registros que se eliminarán en cascada en categorías_productos son aquellos donde category_id = red. No tocará ningún registro donde 'category_id = blue' y no avanzará a la tabla "productos", porque no hay ninguna clave externa definida en esa tabla.

Aquí hay un ejemplo más concreto:

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

Digamos que eliminas la categoría #2 (azul):

DELETE FROM categories WHERE (id = 2);

el DBMS mirará todas las tablas que tienen una clave externa apuntando a la tabla 'categorías' y eliminará los registros donde la identificación coincidente es 2. Dado que solo definimos la relación de clave externa en products_categories, terminará con esta tabla una vez que eliminar completa:

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

No hay ninguna clave externa definida en la productstabla, por lo que la cascada no funcionará allí, por lo que aún tienes botas y guantes en la lista. Ya no existen las 'botas azules' ni los 'guantes azules'.

May 26 '2010 22:05 Marc B