Handling referential integrity when doing persistence testing using DbUnit

DbUnit is a great tool when testing database persistence codes.

There are many ways to using DbUnit. Anyhow, my preferred way is to delete all records from the database tables and re-populate with test data before each test. For unit testing, I test against a memory database, such as Hypersonic; for integration testing, I test against a database that mirrors the one in production.

However, this approach sometimes will get you into the trouble of referential integrity violation.

For example, I had some integration tests against the PRODUCT table, and later I developd some integration tests against the ORDER table, which has a foreign key dependency on the PRODUCT table.

After checking in the latest changes, the continuous integration test failed because of data integrity violation. The cause was that the PRODUCT integration tests saved some records in both PRODUCT and ORDER table, with some orders referencing some products. When DbUnit tried to delete all records from the PRODUCT table, the database detected the violation.

How to solve this? It's pretty easy, just add an empty ORDER record to the test data XML file that is used to populate the database. Following is a sample of a FlatXmlDataSet file:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
   <product id="1" desc="abc" price="1.23"/>
   <order/>
</dataset>

The trick is that in a FlatXmlDataSet file, the order of the tables defined must follow the dependency, that is, a later defined table can have a dependency on an earlier defined table. When DbUnit deletes records, it will delete records from the last defined table first.