Restrições de chave estrangeira do MySQL, exclusão em cascata

Eu quero usar foreign keys para manter a integridade e evitar órfãos (eu já uso innoDB).

Como eu faço uma instrução SQL que DELETE ON CASCADE?

Se eu excluir uma categoria, como posso ter certeza de que não excluiria produtos que também estejam relacionados a outras categorias.

A tabela dinâmica “categories_products” cria um relacionamento muitos para muitos entre as outras duas tabelas.

categories - id (INT) - name (VARCHAR 255) products - id - name - price categories_products - categories_id - products_id 

Se a sua conexão em cascata excluir um produto nuke porque ele era membro de uma categoria que foi eliminada, você configurou suas foreign keys incorretamente. Dadas as tabelas de exemplo, você deve ter a seguinte configuração de tabela:

 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; 

Dessa forma, você pode excluir um produto OU uma categoria e somente os registros associados em categories_products morrerão ao lado. A cascata não irá mais longe na tree e excluirá a tabela de produto / categoria pai.

por exemplo

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

Se você excluir a categoria “vermelho”, apenas a input “vermelha” na tabela de categorias será desativada, bem como as duas inputs prod / cats: “botas vermelhas” e “peles vermelhas”.

A exclusão não será mais extensa e não eliminará as categorias ‘boots’ e ‘coats’.

seguimento de comentários:

você ainda está entendendo mal como as exclusões em cascata funcionam. Eles afetam apenas as tabelas nas quais o “on delete cascade” é definido. Nesse caso, a cascata é definida na tabela “categories_products”. Se você excluir a categoria “vermelho”, os únicos registros que serão excluídos em cascata em categories_products serão aqueles em que category_id = red . Ele não tocará em nenhum registro onde “category_id = blue” e não seguirá para a tabela “products”, porque não há chave estrangeira definida nessa tabela.

Aqui está um exemplo mais 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 você exclua a categoria nº 2 (azul):

 DELETE FROM categories WHERE (id = 2); 

o SGBD irá olhar para todas as tabelas que possuem uma chave estrangeira apontando para a tabela ‘categorias’, e deletar os registros onde o id correspondente é 2. Uma vez que nós só definimos o relacionamento de chave estrangeira em products_categories , você acaba com esta tabela uma vez a exclusão é concluída:

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

Não há chave estrangeira definida na tabela de products , então a cascata não funcionará lá, então você ainda tem botas e luvas listadas. Não há mais ‘botas azuis’ e não ‘luvas azuis’.

Fiquei confuso com a resposta a esta pergunta, então criei um caso de teste no MySQL, espero que isso ajude

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

Eu acho (não tenho certeza) que as restrições de chave estrangeira não farão exatamente o que você deseja, dado o design da sua tabela. Talvez a melhor coisa a fazer seja definir um procedimento armazenado que excluirá uma categoria da maneira desejada e, em seguida, chamar esse procedimento sempre que quiser excluir uma categoria.

 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 

Você também precisa adicionar as seguintes restrições de chave estrangeira à tabela de vinculação:

 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 

A cláusula CONSTRAINT pode, obviamente, também aparecer na instrução CREATE TABLE.

Tendo criado esses objects de esquema, você pode excluir uma categoria e obter o comportamento desejado emitindo CALL DeleteCategory(category_ID) (onde category_ID é a categoria a ser excluída) e ela se comportará como você deseja. Mas não emita uma consulta DELETE FROM normal, a menos que você queira um comportamento mais padrão (ou seja, exclua somente da tabela de vinculação e deixe a tabela de products sozinha).