MySQL foreign key constraints, cascade delete

Question

If your cascading deletes nuke a product because it was a member of a category that was killed, then you've set up your foreign keys improperly. Given your example tables, you should have the following table setup:

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;

This way, you can delete a product OR a category, and only the associated records in categories_products will die alongside. The cascade won't travel farther up the tree and delete the parent product/category table.

e.g.

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

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

If you delete the 'red' category, then only the 'red' entry in the categories table dies, as well as the two entries prod/cats: 'red boots' and 'red coats'.

The delete will not cascade any farther and will not take out the 'boots' and 'coats' categories.

comment followup:

you're still misunderstanding how cascaded deletes work. They only affect the tables in which the "on delete cascade" is defined. In this case, the cascade is set in the "categories_products" table. If you delete the 'red' category, the only records that will cascade delete in categories_products are those where category_id = red. It won't touch any records where 'category_id = blue', and it would not travel onwards to the "products" table, because there's no foreign key defined in that table.

Here's a more concrete example:

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

Let's say you delete category #2 (blue):

DELETE FROM categories WHERE (id = 2);

the DBMS will look at all the tables which have a foreign key pointing at the 'categories' table, and delete the records where the matching id is 2. Since we only defined the foreign key relationship in products_categories, you end up with this table once the delete completes:

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

There's no foreign key defined in the products table, so the cascade will not work there, so you've still got boots and mittens listed. There's just no 'blue boots' and no 'blue mittens' anymore.

I got confused by the answer to this question, so I created a test case in MySQL, hope this helps

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

I think (I'm not certain) that foreign key constraints won't do precisely what you want given your table design. Perhaps the best thing to do is to define a stored procedure that will delete a category the way you want, and then call that procedure whenever you want to delete a category.

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

You also need to add the following foreign key constraints to the linking table:

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

The CONSTRAINT clause can, of course, also appear in the CREATE TABLE statement.

Having created these schema objects, you can delete a category and get the behaviour you want by issuing CALL DeleteCategory(category_ID) (where category_ID is the category to be deleted), and it will behave how you want. But don't issue a normal DELETE FROM query, unless you want more standard behaviour (i.e. delete from the linking table only, and leave the products table alone).