21

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

Mat
  • 82,161
  • 34
  • 89
  • 109
  • If you "DELETE FROM `recursive` ORDER BY `id`" you'll get it work. If, however, you have self-referencing rows, you're pretty much without elegant choices: http://bugs.mysql.com/bug.php?id=7412. The one disabling foreign_keys temporarily is the best. – András Gyömrey Nov 19 '11 at 18:06
  • 1
    One of the few cases that oracle does better, as in oracle you can defare to the checking on constraists until the transaction commits. – Ian Ringrose Dec 19 '13 at 13:35

6 Answers6

38

Why not:

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

?

Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
  • 3
    The question "What is the best way to empty a self-referential MySQL table?" or a form of it appears at so many internet sites and is even duplicated multiple times at SO, and yet so few of the questions have this simple, elegant, and correct answer. +1 – Barzee Feb 27 '15 at 23:14
19

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.

Noah Goodrich
  • 24,875
  • 14
  • 66
  • 96
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.

Powerlord
  • 87,612
  • 17
  • 125
  • 175
0

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

BradC
  • 39,306
  • 13
  • 73
  • 89
-1

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

Assaf Lavie
  • 73,079
  • 34
  • 148
  • 203
-2

delete from table_1 where date(table_1_TIME) < (select T.t_Date from (select max(date(table_1_TIME)) as t_Date from table_1 ) as T)

BAPU SHINDE
  • 1