referente - restricciones en tablas mysql

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

Question

Quiero usar claves externas para mantener la integridad y evitar huérfanos (ya uso innoDB).

¿Cómo puedo hacer una declaración de SQL que DELETE EN CASCADE?

Si borro una categoría, ¿cómo me aseguro de que no elimine productos que también están relacionados con otras categorías?

La tabla dinámica "categories_products" crea una relación many-to-many entre las otras dos tablas.

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

products
- id
- name
- price

categories_products
- categories_id
- products_id

Answer 1

Creo (no estoy seguro) que las restricciones de clave externa no harán exactamente lo que usted desea dado el diseño de su mesa. Quizás lo mejor que se puede hacer es definir un procedimiento almacenado que elimine una categoría de la manera que desee, y luego llamar a ese procedimiento siempre que quiera eliminar una categoría.

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

También necesita agregar las siguientes restricciones de clave externa a la tabla de enlace:

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 cláusula CONSTRAINT puede, por supuesto, aparecer también en la sentencia CREATE TABLE.

Una vez creados estos objetos de esquema, puede eliminar una categoría y obtener el comportamiento que desea emitiendo CALL DeleteCategory(category_ID) (donde category_ID es la categoría que se eliminará), y se comportará como desee. Pero no emita una consulta DELETE FROM normal, a menos que desee un comportamiento más estándar (es decir, elimine solo de la tabla de enlaces y deje solo la tabla de products ).

Answer 2

Me confundí con la respuesta a esta pregunta, así que creé un caso de prueba en MySQL, espero que esto ayude

-- Schema
CREATE TABLE T1 (
    `ID` int not null auto_increment,
    `Label` varchar(50),
    primary key (`ID`)
);

CREATE TABLE T2 (
    `ID` int not null auto_increment,
    `Label` varchar(50),
    primary key (`ID`)
);

CREATE TABLE TT (
    `IDT1` int not null,
    `IDT2` int not null,
    primary key (`IDT1`,`IDT2`)
);

ALTER TABLE `TT`
    ADD CONSTRAINT `fk_tt_t1` FOREIGN KEY (`IDT1`) REFERENCES `T1`(`ID`) ON DELETE CASCADE,
    ADD CONSTRAINT `fk_tt_t2` FOREIGN KEY (`IDT2`) REFERENCES `T2`(`ID`) ON DELETE CASCADE;

-- Data
INSERT INTO `T1` (`Label`) VALUES (''T1V1''),(''T1V2''),(''T1V3''),(''T1V4'');
INSERT INTO `T2` (`Label`) VALUES (''T2V1''),(''T2V2''),(''T2V3''),(''T2V4'');
INSERT INTO `TT` (`IDT1`,`IDT2`) VALUES
(1,1),(1,2),(1,3),(1,4),
(2,1),(2,2),(2,3),(2,4),
(3,1),(3,2),(3,3),(3,4),
(4,1),(4,2),(4,3),(4,4);

-- Delete
DELETE FROM `T2` WHERE `ID`=4; -- Delete one field, all the associated fields on tt, will be deleted, no change in T1
TRUNCATE `T2`; -- Can''t truncate a table with a referenced field
DELETE FROM `T2`; -- This will do the job, delete all fields from T2, and all associations from TT, no change in T1

Answer 3

Si su cascada elimina un producto nuclear debido a que fue un miembro de una categoría que fue eliminada, entonces usted configuró sus claves externas incorrectamente. Dadas sus tablas de ejemplo, debe 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 forma, puede eliminar un producto O una categoría, y solo los registros asociados en categorías_productos morirán al costado. La cascada no viajará más arriba en el árbol y eliminará la tabla de productos / categorías principales.

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 ''rojo'', entonces solo la entrada ''roja'' en la tabla de categorías muere, así como las dos entradas prod / cats: ''botas rojas'' y ''capas rojas''.

La eliminación no continuará en cascada y no eliminará las categorías ''botas'' y ''capas''.

comentario de seguimiento:

todavía no comprendes cómo funcionan las eliminaciones en cascada. Solo afectan las tablas en las que se define "on delete cascade". En este caso, la cascada se establece en la tabla "categories_products". Si elimina la categoría ''rojo'', los únicos registros que eliminarán en cascada en categorías_productos son aquellos en los que category_id = red . No tocará ningún registro donde ''category_id = blue'', y no irá a la tabla de "productos", porque no hay una 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 verá todas las tablas que tienen una clave externa apuntando a la tabla de ''categorías'', y eliminará los registros donde el ID coincidente es 2. Como solo definimos la relación de clave externa en products_categories , terminará con esta tabla una vez la eliminación completa:

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

No hay una clave externa definida en la tabla de products , por lo que la cascada no funcionará allí, por lo que todavía tiene botas y mitones en la lista. Ya no hay ''botas azules'' ni más ''mitones azules''.