TDD with HSQLDB -- removing foreign keys

Question

I'm using HSQLDB for data layer integration testing, which is great. However, I'm finding that my foreign key constraints are getting in the way of my tests. For example, to test a simple select on one table, I have to insert dummy data into five additional tables. This makes me want to throw things.

I have JPA annotations throughout our model code, and have configured Hibernate to recreate the schema (hbm2ddl.create-drop) in configuration. The joins are being interpreted correctly as foreign key constraints when the tables are generated.

What I'd like is to either:

1. Not create the foreign keys initially (ideal, cleanest), or
2. Find a way to programmatically drop all the foreign keys in the database (kinda hacky but will get the job done)

If it's helpful, I'm using Spring to autowire these tests. The tests in question inherit from AbstractTransactionalJUnit4SpringContextTests.

What do you think? Can this be done?

Answer 1

I encountered the exact same trouble while trying to test my DAO with flat xml Dataset. Config is DBunit + HSQLDB 2.2.8 + JUnit4 + Spring + JPA-> all together leading to

java.sql.SQLIntegrityConstraintViolationException: integrity constraint violation: foreign key no parent; FK13EE6CE6F09A6AAC table: ****

I found a nice workaround by implementing a listener extending AbstractTestExecutionListener. You would specify the type of action to be taken before each test, in our case, disabling foreign key constraints. NOTE: the syntax may differ depending of the version of HSQLDB used.

public class ForeignKeyDisabling extends AbstractTestExecutionListener {    
    @Override
    public void beforeTestClass(TestContext testContext) throws Exception {
        IDatabaseConnection dbConn = new DatabaseDataSourceConnection(
                testContext.getApplicationContext().getBean(DataSource.class)
                );
        dbConn.getConnection().prepareStatement("SET DATABASE REFERENTIAL INTEGRITY FALSE").execute();

    }
}

You then only need to add this listener in the collection already in place in your tests:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"applicationContext-test.xml"})
@TestExecutionListeners({DependencyInjectionTestExecutionListener.class, DataSetTestExecutionListener.class, ForeignKeyDisabling.class})