3

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.

Charles
  • 50,943
  • 13
  • 104
  • 142
KenT
  • 33
  • 1
  • 5

4 Answers4

3

You can add a FOREIGN KEY on folders with CASCADE option for DELETE:

ALTER TABLE `folders` ADD CONSTRAINT `FK_folder_parent` FOREIGN KEY `FK_folder_parent` (`parent_key`)
REFERENCES `folders` (`folder_key`)
ON DELETE CASCADE
ON UPDATE CASCADE

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Stelian Matei
  • 11,553
  • 2
  • 25
  • 29
  • What is the error? Bad syntax or do you have values that doesn't match? I see you that the `parent_key` is NOT NULL? You should make it NULL to root folders, right? – Stelian Matei Feb 13 '12 at 11:42
  • thank you very much. I solved my problem by changing the model. – KenT Feb 13 '12 at 14:44
3

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/

SergeS
  • 11,533
  • 3
  • 29
  • 35
2

Instead of storing the "Parent key" value store their id

CREATE TABLE IF NOT EXISTS `folders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `folder_key` varchar(40) NOT NULL,
  `parent_key` int(11) NOT NULL,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
foreign key(`parent_key`) on delete cascade
) ENGINE=InnoDB;
Naveen Kumar
  • 4,543
  • 1
  • 18
  • 36
0

@Naveen

you should use the same datatypes when creating a cascade delete constraint. Now you have an unsigned int and a signed int.

rubyan
  • 51
  • 1
  • 5