How to Use on Delete Cascade in MySQL

MySQL foreign key constraints, cascade delete

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.

How do I use on delete cascade in mysql?

Here's what you'd include in your components table.

CREATE TABLE `components` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `typeId` int(10) unsigned NOT NULL,
    `moreInfo` VARCHAR(32), 
    -- etc
    PRIMARY KEY (`id`),
    KEY `type` (`typeId`)
    CONSTRAINT `myForeignKey` FOREIGN KEY (`typeId`)
      REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

Just remember that you need to use the InnoDB storage engine: the default MyISAM storage engine doesn't support foreign keys.

How to add on delete cascade option in mysql?

You have a typo in the Order table. You have PRIMAY where it should be PRIMARY.

After correcting this, I tried creating the tables and all statements worked fine, including the last one.

Cascading deletes like ON DELETE CASCADE for a one time operation in MySQL

No, the simple answer is, no, there is no shortcut.

You either write down DELETE statements to delete all the related rows in the related tables or you have defined foreign key constraints with ON DELETE CASCADE.

Note that - as long as there are no circular paths in the foreign key relationships - it is possible to use a single DELETE statement that deletes from multiple tables:

DELETE a, b, c, d
FROM a
  LEFT JOIN b  ON  b.a_id = a.a_id
  LEFT JOIN c  ON  c.a_id = a.a_id
  LEFT JOIN d  ON  d.b_id = b.b_id 
WHERE
    a.a_id = 1 ;

Mysql on delete cascade not working

The constrain you have now will delete from tags_files when a referenced id will be deleted on files.

If you need to automatically delete from files when you delete from tags_files, then the constrain must be on files table.

Like this:

DROP TABLE IF EXISTS `files`;
CREATE TABLE IF NOT EXISTS `files` (
    `id` VARCHAR(36)  NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `extension` VARCHAR(5) NOT NULL,
    `version` INT(11) NOT NULL,
    `date` DATE NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT FOREIGN KEY (`id`) REFERENCES `tags_files` (`file_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
and TagsFiles:

DROP TABLE If EXISTS  `tags_files`;
CREATE TABLE IF NOT EXISTS `tags_files` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `tag_id` INT(11) NOT NULL,
    `file_id` VARCHAR(36) NOT NULL,
    PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Well you get an error in above example because MySQL don't allow constraints for varchar type. If you change it to char you can. But you also need to change the sequence of the execution queries due to constrains. Do like this:

DROP TABLE IF EXISTS `files`;
DROP TABLE If EXISTS  `tags_files`;

CREATE TABLE IF NOT EXISTS `tags_files` (
    `id` INT(11) NOT NULL,
    `tag_id` INT(11) NOT NULL,
    `file_id` char(36) NOT NULL,
    PRIMARY KEY (`file_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `files` (
    `id` char(36)  NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `extension` VARCHAR(5) NOT NULL,
    `version` INT(11) NOT NULL,
    `date` DATE NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT FOREIGN KEY (`id`) REFERENCES `tags_files` (`file_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

Related Topics