title: Delete all data from MySQL table which has a foreign key constraint to itself with DBUnit
body: <p>I am using <a href="http://dbunit.sourceforge.net/" rel="nofollow noreferrer">DBUnit</a> with a MySQL database. The DBUnit default <a href="http://dbunit.sourceforge.net/components.html#cleanInsert" rel="nofollow noreferrer">CLEAN_INSERT</a> 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: </p>
  
  <blockquote>
    <p>Cannot delete or update a parent row: a foreign key constraint fails</p>
  </blockquote>
  
  <p>How should I configure DBUnit to clean the test data correctly in this case?</p>
  
  <p><a href="https://stackoverflow.com/a/4573550/1095318">One possibility</a> 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.</p>
  
  <p><a href="https://stackoverflow.com/questions/13795052/deleting-from-a-mysql-table-with-foreign-key-constraints">Deleting from a MySQL table with foreign key constraints</a> 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.</p>
  
  <h3>Background Information</h3>
  
  <p>Apparently this is a <a href="https://stackoverflow.com/a/7847550/1095318">missing feature</a> in MySQL. Quoting from <a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html" rel="nofollow noreferrer">InnoDB and FOREIGN KEY Constraints</a>:</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>Here is a SQL script which reproduces the error:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The delete statement fails with error</p>
  
  <blockquote>
    <p>ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (<code>coverage_integration_test</code>.<code>foo</code>, CONSTRAINT <code>FK_other_foo_id</code> FOREIGN KEY (<code>other_foo_id</code>) REFERENCES <code>foo</code> (<code>foo_id</code>))</p>
  </blockquote>
  
  <p>Tested with MySQL 5.5.40.</p>