DBUnit/HSQL how to initialize db with table having self foreign keys?

Question

I would like to initialize HSQL db with DBUnit. I have a table like this one:

table EMPLOYEE( id, name, manager_id) // manager_id is nullable
     CONSTRAINT "fk_manager" FOREIGN KEY ("manager_id")
     REFERENCES EMPLOYEE("id)

The problem is when I try to initialize with this dataset:

<dataset>
  <EMPLOYEE id="2" name="John" manager_id="5"/> <!--Doe is the John's manager-->
  <EMPLOYEE id="3" name="Doe" manager_id="4"/> <!--Kong is the Does's manager-->
  <EMPLOYEE id="4" name="Kong" manager_id="2"/> <!-- John is the Kong's manager-->
</dataset>

I have this exception:

Caused by: org.hsqldb.HsqlException: integrity constraint violation: foreign key no parent; fk_manager table: EMPLOYEE

How to solve that error? Thanks

Answer 1

You need to insert the first row without the managerId and add this value after inserting the row containing its managerId

INSERT INTO EMPLOYEE (id, name, managerId) VALUES (2, 'John', null)
INSERT INTO EMPLOYEE (id, name, managerId) (VALUES 4, 'Kong', 2)
INSERT INTO EMPLOYEE (id, name, managerId) (VALUES 3, 'Doe', 4)
UPDATE EMPLOYEE SET managerId = 3 WHERE id = 2 

It is also possible to turn off foreign key constraint checks during the inserts. Your dataset should be consistent and should not include foreign key references to non-existent rows.

http://hsqldb.org/doc/2.0/guide/management-chapt.html#mtc_database_settings

SET DATABASE REFERENTIAL INTEGRITY FALSE
-- perform the inserts
SET DATABASE REFERENTIAL INTEGRITY TRUE