Tuesday, April 24, 2007

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.

  • 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!!


  1. You can set this ID manually. I also had this problem using Oracle. In the Oracle case, it lets me specify the ID - it generates it if I omit it.

    Maybe, in SQLServer, you can't specify it... In this case, I think you should look for some kind of solution that allow for this option of specifying ID.

    I think you won't be successful in another way.


  2. Hello,

    I ran across this thread trying to generate test data for a production database.

    I have the same issues namely an Oracle database that has lots of tables having lots of relationships with foreign key constraints based on primary keys. This is effectively the representation of a network, and DbUnit does not allow you to specify these relationships based on primary keys generated from sequences by triggers on insert.

    The more that I have been looking on this, the less that I find. It's hard to believe that so few have perceived this as a problem.

    Old Lycos Chuck

  3. Who knows where to download XRumer 5.0 Palladium?
    Help, please. All recommend this program to effectively advertise on the Internet, this is the best program!

  4. I stumbled onto the same issue. In my opinion this case - whioch in fact is really common - makes the strength of dbunit completely useless, because you are forced to enter data manually, read out manually, and update specific lines manually. DBUnit does not save work here :(


Tweety thoughts

    follow me on Twitter