MySQL外键约束，级联删除

Question

MySQL外键约束，级联删除我想使用外键来保持完整性和避免孤儿(我已经使用了InnoDB)。如何在级联上创建删除的SQL语句？如果我删除一个类别，那么如何确保它不会删除与其他类别也相关的产品。Pivot表“类别_产品”在另外两个表之间创建了一个多到多的关系。categories- id (INT)- name (VARCHAR 255)products- id- name- price categories_products- categories_id- products_id

Answer 1

如果您的级联删除一个产品，因为它是一个类别的成员被杀死，那么你设置你的外键不当。给定示例表，您应该设置了以下表：

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中的相关记录才会同时消亡。级联将不会沿着树的更远的方向移动，也不会删除父产品/类别表。

G.

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

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

如果删除“红色”类别，那么只有“类别”表中的“红色”条目死亡，以及两个条目“红色靴子”和“红色外套”。

删除将不再级联，也不会删除‘靴子’和‘外套’类别。

评论后续行动：

你还是误解了级联删除的工作原理。它们只影响定义“ON DELETE级联”的表。在本例中，级联是在“类别_产品”表中设置的。如果删除“红色”类别，那么在类别_Products中唯一会级联删除的记录是category_id = red..它不会触及‘分类_id=蓝色’的任何记录，也不会继续到“Products”表，因为该表中没有外键定义。

下面是一个更具体的例子：

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

假设您删除了类别2(蓝色)：

DELETE FROM categories WHERE (id = 2);

DBMS将查看所有具有指向“类别”表的外键的表，并删除匹配id为2的记录。products_categories，删除完成后，您将得到此表：

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

中没有定义外键。products表，因此级联将不能在那里工作，所以您仍然有靴子和手套列出。再也没有“蓝色靴子”和“蓝色手套”了。

Answer 2

我被这个问题的答案弄糊涂了，所以我在MySQL中创建了一个测试用例，希望这会有所帮助。

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

Answer 3

我认为(我不确定)外键约束不能精确地满足您的表设计要求。也许最好的做法是定义一个存储过程，该存储过程将以您想要的方式删除某个类别，然后在您想要删除某个类别时调用该过程。

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

约束子句当然也可以出现在CREATETABLE语句中。

创建了这些模式对象之后，可以删除一个类别，并通过发出CALL DeleteCategory(category_ID)(其中，类别_ID是要删除的类别)，它的行为将按您的要求进行。但不要发出正常的DELETE FROM查询，除非需要更多的标准行为(即仅从链接表中删除，然后保留products(单靠桌子)。