MySQL foreign key constraints, cascade delete

I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB).

How do I make a SQL statment that DELETE ON CASCADE?

If I delete a category then how do I make sure that it would not delete 

products that also are related to other categories.

The pivot table "categories_products" creates a many-to-many relationship 

between the two other tables.

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

products
- id
- name
- price

categories_products
- categories_id
- products_id

 Answers

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

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