DBUnit + Sequence numbers + Foreign keys = Nightmare!

Setting up my unit tests with reliable fixtures is turning out to be quite a headache.

Sparing you arcane application-speak let me use the classic simple EMPLOYEE-DEPARTMENT example to illustrate my problem.

Facts:

• We have the typical EMPLOYEE table and the DEPARTMENT table
• Both use database-generated ids (sequence numbers) as primary keys.
• An EMPLOYEE belongs to a DEPARMENT.
• The DEPT_ID field in the EMPLOYEE table is a foreign key into the ID field of the DEPARTMENT table.

So far, so good.

Now, consider a method in the EmploymentDao class that returns all known employments. In SQL speak, it executes

Select e.emp_name, d.dept_name
From employee e, department d
Where e.dept_id=d.id

In order to test this method I need to set up the database with necessary data. I would like to be able to use something like DBUnit to set up my fixtures. However, I have to populate the DEPARTMENT table with at least one department record. Then, I need to get the ID field of that record to insert a record into the EMPLOYEE table. How does one do that ? This is crucial for a test such as

public void testGettingAllEmployments() {
  List employments = employmentDao.getAllEmployments();
  assertNotNull("Empty list of employments", employments);
}

Even with a simplification of suppressing foreign key constraints, I will have to do something like:

• Insert a record into the DEPARTMENT table with a DBUnit fixture.
  
• Insert a record in the EMPLOYEE table (with a bogus dept_id)
• Then read the record, from the DEPARTMENT table to get at its id
• Update the record in theEMPLOYEE table to set the dept_id

Wow! That's a lot of preamble -- not to mention using of the very DAO methods I want to test!!