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

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开发者_如何学Go to delete the whole table's contents, not the tables themselves.

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

This should do the trick:

TRUNCATE table_name;

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)

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;

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;