Spring – TDD with HSQLDB — removing foreign keys

foreign-keyshibernatehsqldbspringtdd

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:

Not create the foreign keys initially (ideal, cleanest), or
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?

Best Answer

You can deactivate FK constraints with the following instruction:

SET REFERENTIAL_INTEGRITY FALSE;

You could execute it via a JDBC Statement before your test methods (and set it back to TRUE after).

Related Solutions

Sql-server – How to foreign key constraints be temporarily disabled using T-SQL

If you want to disable all constraints in the database just run this code:

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

I find it useful when populating data from one database to another. It is much better approach than dropping constraints. As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

If you are deleting all the data you may find this solution to be helpful.

Also sometimes it is handy to disable all triggers as well, you can see the complete solution here.

Foreign Key naming scheme

The standard convention in SQL Server is:

FK_ForeignKeyTable_PrimaryKeyTable

So, for example, the key between notes and tasks would be:

FK_note_task

And the key between tasks and users would be:

FK_task_user

This gives you an 'at a glance' view of which tables are involved in the key, so it makes it easy to see which tables a particular one (the first one named) depends on (the second one named). In this scenario the complete set of keys would be:

FK_task_user
FK_note_task
FK_note_user

So you can see that tasks depend on users, and notes depend on both tasks and users.

Best Answer

Related Solutions

Sql-server – How to foreign key constraints be temporarily disabled using T-SQL

Foreign Key naming scheme

Related Topic