Delete all data from MySQL table which has a foreign key constraint to itself with DBUnit

Question

I am using DBUnit with a MySQL database. The DBUnit default CLEAN_INSERT issues a "DELETE FROM" statement without WHERE clause for the tables with test data. With MySQL this fails for a table which has a foreign key constraint to itself with the following error message:

Cannot delete or update a parent row: a foreign key constraint fails

How should I configure DBUnit to clean the test data correctly in this case?

One possibility is to disable foreign key checks for the session. However I am not sure where the best place to do this is in DBUnit. I would not want to disable the checks for the code under test itself, but at most for the setup/teardown of the test data.

Deleting from a MySQL table with foreign key constraints asks a similar question, but there the foreign key constraint references a different table. The solution of adding ON DELETE CASCADE to the foreign key definition works but I don't want to alter the table definition for my test.

Background Information

Apparently this is a missing feature in MySQL. Quoting from InnoDB and FOREIGN KEY Constraints:

Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. ... InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself using a foreign key.

Here is a SQL script which reproduces the error:

create table foo (
    foo_id varchar(255) NOT NULL,
    other_foo_id varchar(255),
    PRIMARY KEY (foo_id),
    CONSTRAINT FK_other_foo_id FOREIGN KEY (other_foo_id) REFERENCES foo (foo_id)
);

insert into foo values ('foo-id-1',null);
insert into foo values ('foo-id-2','foo-id-1');

delete from foo;

The delete statement fails with error

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (coverage_integration_test.foo, CONSTRAINT FK_other_foo_id FOREIGN KEY (other_foo_id) REFERENCES foo (foo_id))

Tested with MySQL 5.5.40.