Search code examples
mysqlrecursioncascading-deletes

How to recursively delete items from table?

I've a MySQL table "folders":

CREATE TABLE IF NOT EXISTS `folders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `folder_key` varchar(40) NOT NULL,
  `parent_key` varchar(40) NOT NULL,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

I don't use integer IDs, only keys (alphanumeric hashes, which I've replaced with words to make things more clear). So, folder_key & parent_key are SHA-1 hashes (in my real application).

INSERT INTO `folders` (`id`, `folder_key`, `parent_key`, `name`) VALUES
(1, 'sun', 'root', '1'),
(2, 'moon', 'sun', '1.1'),
(3, 'jupiter', 'moon', '1.1.1'),
(4, 'mars', 'root', '2');

As you can see the first item has a parent_key too, it's a root key.

The test case: If I wish to delete an item with folder_key === moon (1.1), it should also delete its children element(s), in this case it's an item with folder_key === jupiter (1.1.1) and so on...

Let say I wish to delete multiple items, so I do:

DELETE from folders WHERE folder_key IN('moon', 'mars'); After execution, the table should have only one item with folder_key === sun

So, the question is: How to delete items from that table having one or more folder_keys (recursively) with MySQL triggers, ON DELETE CASCADE or ... ?

Thanks.

Solution

  • With this model it is imposibble

    1. Instead of root use NULL , so you can use InnoDB Foreign Key + Cascade Delete.
    2. Instead of using string parent_key, use id ( eg. sun = 1, moon = 2 )

    Other way is to change data model, so you can easly select any descendands of element - see this for example http://www.sitepoint.com/hierarchical-data-database-2/