MySQL外鍵約束，級聯刪除我想使用外鍵來保持完整性和避免孤兒(我已經使用了InnoDB)。如何在級聯上創(chuàng)建刪除的SQL語句？如果我刪除一個類別，那么如何確保它不會刪除與其他類別也相關的產品。Pivot表“類別_產品”在另外兩個表之間創(chuàng)建了一個多到多的關系。categories- id (INT)- name (VARCHAR 255)products- id- name- price
categories_products- categories_id- products_id
3 回答
慕田峪9158850
TA貢獻1794條經驗 獲得超7個贊
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;
products: boots, mittens, hats, coats categories: red, green, blue, white, black prod/cats: red boots, green mittens, red coats, black hats
category_id = red
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+------------+-------------+
DELETE FROM categories WHERE (id = 2);
products_categories
+------------+-------------+| product_id | category_id |+------------+-------------+| 1 | 1 | // red mittens| 2 | 1 | // red boots+------------+-------------+
products
30秒到達戰(zhàn)場
TA貢獻1828條經驗 獲得超6個贊
-- SchemaCREATE 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;-- DataINSERT 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);-- DeleteDELETE FROM `T2` WHERE `ID`=4; -- Delete one field, all the associated fields on tt, will be deleted, no change in T1TRUNCATE `T2`; -- Can't truncate a table with a referenced fieldDELETE FROM `T2`; -- This will do the job, delete all fields from T2, and all associations from TT, no change in T1
人到中年有點甜
TA貢獻1895條經驗 獲得超7個贊
CREATE PROCEDURE `DeleteCategory` (IN category_ID INT)LANGUAGE SQLNOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINERBEGINDELETE FROM `products`WHERE `id` IN ( SELECT `products_id` FROM `categories_products` WHERE `categories_id` = category_ID );DELETE FROM `categories`WHERE `id` = category_ID;END
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
CALL DeleteCategory(category_ID)DELETE FROMproducts
添加回答
舉報
0/150
提交
取消