How do I delete all the rows of a table with a recursive structure (MySQL)?

Question

I have a table in my DB, in which every row has a parent id which is the id of another row in the table (the table represents a tree-like structure). I would like to empty the table. But when I perform

DELETE FROM table_name WHERE true;

I get an error (foreign key constraint). How do I empty the table anyway?

Clarification: I want to delete the whole table's contents, not the tables themselves.

I'm not sure if I understand the question, do you want to delete the whole table's contents? If yes, `TRUNCATE table_name;` will do it for you. — Andre, Dec 20 '10 at 23:10
possible duplicate of [What is the best way to empty a self-referential MySQL table?](http://stackoverflow.com/questions/615797/what-is-the-best-way-to-empty-a-self-referential-mysql-table) — Barzee, Feb 27 '15 at 23:12

score 4 · Accepted Answer · edited Dec 20 '10 at 23:13

4

When you create your foreign key relationships, you need to specify on delete cascade.

EDIT: There's a pretty good reference right here: http://en.wikipedia.org/wiki/Foreign_key

edited Dec 20 '10 at 23:13

jwueller

30,582
4
66
70

answered Dec 20 '10 at 23:11

Madison Caldwell

862
1
7
16

1

+1! That is probably what the OP needs, although he did not ask for it directly. – jwueller Dec 20 '10 at 23:14

score 1 · Answer 2 · answered Dec 20 '10 at 23:11

1

This should do the trick:

TRUNCATE table_name;

answered Dec 20 '10 at 23:11

jwueller

30,582
4
66
70

I get the same error. I think that my SQL statement is equivalent to yours. – snakile Dec 20 '10 at 23:41
@snakile: Interesting. I was pretty sure that this would work. – jwueller Dec 20 '10 at 23:42
Same issue, but this worked perfectly for me. I would 100% prefer an adjustment to the query versus an adjustment to the schema for On Delete Cascade. – I. Buchan Nov 16 '17 at 16:48

score 1 · Answer 3 · answered Dec 20 '10 at 23:52

1

If you can't change the ON DELETE behavior, you can do this repeatedly until the table is empty:

DELETE FROM table_name WHERE id NOT IN (SELECT parent_id FROM table_name)

answered Dec 20 '10 at 23:52

phoog

42,068
6
79
117

score 0 · Answer 4 · edited Jul 17 '15 at 18:45

0

An old thread but I'll post my answer just to help anyone reading this question.

I had the same issue and I ended up setting the parent column to null before executing the delete statement.

UPDATE table_name SET parent_id=null WHERE true;

DELETE FROM table_name WHERE true;

edited Jul 17 '15 at 18:45

josliber

43,891
12
98
133

answered Jul 17 '15 at 18:33

clavarreda

21
3

score 0 · Answer 5 · answered Dec 20 '10 at 23:12

0

First delete the rows that have no children.

So if the id foreign key is parent_id, do something like:

DELETE FROM table_name WHERE parent_id IS NOT NULL;

Then delete the rest:

DELETE FROM table_name;

answered Dec 20 '10 at 23:12

zsalzbank

9,685
1
26
39

How do I delete all the rows of a table with a recursive structure (MySQL)?

5 Answers5