What is the best way to empty a self-referential MySQL table?

Question

I have a self-referential MySQL table with a recursive parent_id:

CREATE TABLE `recursive` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) default NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `data_categorysource_parent_id` (`parent_id`),
  CONSTRAINT `parent_id_refs_id_627b4293`
    FOREIGN KEY (`parent_id`) REFERENCES `data_categorysource` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

During testing, I want to empty it but TRUNCATE fails:

TRUNCATE `recursive` 
/* SQL Error: Cannot delete or update a parent row: a foreign key
constraint fails...

I currently have to manually delete all records, starting at the bottom of the tree working upwards. This gets onerous even with small trees.

Is there an easy way around this? I can't DROP the table and re-create it easily as other tables reference it (I have already truncated those so there should be no data integrity issues there).

Answer 1

Well, you could add an ON DELETE CASCADE to the FOREIGN KEY definition... at least temporarily. That would allow you to truncate the table by removing the referenced rows first.

There are other ON DELETE types as well; the default is ON DELETE NO ACTION.

Answer 2

Why not:

UPDATE 'recursive' SET 'parent_id' = NULL WHERE 'parent_id' IS NOT NULL;
DELETE FROM 'recursive';

?

Answer 3

Repeatedly select the rows that do not appear as parents and delete them, until the table is empty. (Assuming there are no cycles...)

Answer 4

Or just remove the (recursive) foreign key constraint, then truncate the table, then re-add the contraint.

Answer 5

If you just want to empty the whole thing for testing purposes use:

SET FOREIGN_KEY_CHECKS = 0;

// Execute Query

SET FOREIGN_KEY_CHECKS = 1;

This totally bypasses any foreign key checks.