Raible Designs

DbUnit Tip: Turn off foreign key constraints when importing into MySQL

One of the issues I've had with using DbUnit is getting tables to load in the proper order from XML. The XML datasets I use to load table data are flat and don't really have any notion of foreign keys and such. Therefore, when you get into a situation where tables have a circular reference, using DbUnit can be a real bitch. I ran into this situation yesterday.

Luckily, I was able to figure out a solution thanks to the help of Mark Matthews. Just add "sessionVariables=FOREIGN_KEY_CHECKS=0" to your JDBC URL. Here's how the "db-load" target in AppFuse looks with this in place:

    <target name="db-load" depends="prepare"
        description="Loads the database with sample data">
        <property name="operation" value="CLEAN_INSERT"/>
        <property name="file" value="metadata/sql/sample-data.xml"/>
        <dbunit driver="${database.driver_class}"
            supportBatchStatement="false" 
            url="${database.url}&amp;sessionVariables=FOREIGN_KEY_CHECKS=0"
            userid="${database.username}" password="${database.password}">
            <operation type="${operation}" src="${file}" format="xml" transaction="true"/>
        </dbunit>

    </target>

Does your preferred database have a similar mechanism for turning off foreign key checks using the connection URL?

Posted in Java at Mar 01 2006, 04:16:48 PM MST 25 Comments

// Get the database connection Connection jdbcConnection = dataSource.getConnection(); DatabaseConfig dbConfig = new DatabaseConfig(); dbConfig.setFeature(DatabaseConfig.FEATURE_QUALIFIED_TABLE_NAMES,true); IDatabaseConnection connection = new databaseConnection(jdbcConnection,schema); try { //initialize your dataset here IDataSet dataSet = new FlatXmlDataSet(new File(dataSetName)); // This operation decorates an insert operation and disables the MS SQL Server automatic // identifier generation (IDENTITY) during its execution InsertIdentityOperation.CLEAN_INSERT.execute(connection, dataSet); return 1; } catch (Exception ex) { ex.printStackTrace(); throw new ServiceUnavailableException(); } finally { if (connection != null) connection.close(); }

<replaceregexp file="boctest.sql" flags="sg" match='(add constraint [^;]+ references [^ )]+);' replace="\1 deferrable initially deferred;"/>

Comments:

Use DDLUtils instead of DBUnit directly. It has a lot of improvements and better ANT tasks.

Posted by Ahmed Mohombe on March 02, 2006 at 07:02 AM MST #

Last time I used dbUnit was a couple of years ago, against Oracle 9. Things may have changed since then. Our solution was a bit more convoluted than we wanted. On an Oracle jdbc connection, open a Statement and execute "SET CONSTRAINTS ALL DEFERRED", then constraint checking is delayed until the final commit. Close the Statement, keep the Connection and use it as the connection for the data load/drop. To prevent pasting that around, we wrote our own "DeferConstraintsCheckinOperation" that extends dbunit's DatabaseOperation, and wrapped our dbunit operations with our operation.

Posted by Paul Devine on March 02, 2006 at 03:57 PM MST #

You can do something similar to the Oracle thing in PostgreSQL, see here . You need to explicitly mark the FKC's as deferrable, I dunno if that's the way Oracle does it.

I just had a John McEnroe moment (ie "you cannot be serious") reading about the MySQL behaviour. From what I can see, the implication is that the checking is completely turned off, not just delayed until the end of the transaction. What happens if the data's bad? It will happily insert your bad data. This seems unspeakably evil to me, although it's generally in line with the MySQL way of doing things. Have I misread?

Posted by Tom Dunstan on March 06, 2006 at 02:27 AM MST #

I do not think transaction is supported in DBUnit 2.1. To use this you may need to upgrade to DBUnit-dev which is version 2.2 I guess.

Posted by Thomas Ramapuram on March 07, 2006 at 11:46 PM MST #

Thomas - you are correct about transaction="true", I'm using a nightly build of DbUnit.

Posted by Matt Raible on March 08, 2006 at 08:12 AM MST #

Paul - does "SET CONSTRAINTS ALL DEFERRED" on Oracle delay all constraints or only the deferrable ones?

Posted by Vahan Harput on March 12, 2006 at 01:47 PM MST #

I hadn't seen an example of passing query string params in the jdbc url. (Now I've got a research/learning topic for the afternoon.) The example above didn't initially work for me, since I didn't already have a ? in my url. (I bet your database.url property already has params, so it already had one.) So, here's what config ended up looking like:

Posted by Trenton Lipscomb on April 03, 2006 at 12:05 PM MDT #

I'm trying to integrate DBUnit into some unit tests programmatically and I'm running into a similar issue. I do not want to turn off foreign keys, which is easy enough to deal w/ if your tables are always clean at the start of the test. But ideally, each test should not have to worry about the db state. For example if I have test XXX execute, inserting into tables A, B, and C. C contains a FK to A. Test YYY tries to insert a new row into C, but in the process of a clean_insert, cannot delete all rows b/c of the constraint. I feel like turning off this constraint checking is ok w/ strict data insertion, but if the same connection is used to commit objects during tests, this constraint should not be toggled off. Does anyone have a good solution for leaving FK constraints intact, but also utilizing DBUnit's ease of data insertion? I've considered rolling back every test's data, but thought there might be a more elegant solution.

Posted by Tom on May 24, 2006 at 10:53 AM MDT #

if you're not into using a nightly of DBUnit, I posted a blog entry with an alternative solution for MySQL etc.

http://blogs.sourceallies.com/roller/page/joedeveloper?entry=dbunit_ant_task_and_foreign

There's a bit of code, but it will compile against the latest release of DBUnit and allow you to execute a script as part of the ant task. In said script you can utilize the connection that is being used to disable the FKChecking. It was the only way I could get things to work as the above listed method gave me all sorts of fits.

The extension shown also allows other options for use when using DBUnit for pre and post scripting. more concise than a bunch of sql targets IMHO.

Posted by Brian Greene on July 13, 2006 at 09:47 AM MDT #

To Tom:

I also use DbUnit (2.1) programatically. What I've found works (and works pretty well for us) is subclassing DatabaseTestCase and using that for all our MySQL database test cases.

In my getConnection() method, I simply turn off foreign key checking using "SET FOREIGN_KEY_CHECKS=0". This lets DbUnit load my test data with no hiccups.

However, since a) that's a session variable, and b) the actual tests get their own connections directly from the datasource (I use Spring JdbcDaoSupport), then the actual DAO operations execute with foreign key constraint checks on - thus making my tests more robust and in line with the production environment.

Posted by Alistair Israel on September 05, 2006 at 11:20 PM MDT #

How to SET FOREIGN_KEY_CHECKS=0 in getConnection() method
Is there a way to include this in Java code rather then specify in the Ant task.
Is there an alternate solution for SQLServer2005?

Following is the code I'm using to load the data into DB from an XML dataset. How do I set the FOREIGN_KEY_CHECKS=0 in the code here ??

Posted by sageer on November 07, 2006 at 08:43 PM MST #

Can someone post some example code showing exactly how to disable foreign key checking in the getConnection() method, as mentioned by Alistair? Will this work for HSQLDB, or is it specific to MySQL?

I have tried appending "&sessionVariables=FOREIGN_KEY_CHECKS=0" to my JDBC URL, with no effect, and Alistair's approach sounds better anyway.

Thanks in advance.

--James

Posted by James Adams on December 27, 2006 at 01:02 AM MST #

James, Use "?sessionVariables=FOREIGN_KEY_CHECKS=0" on your DriverManager.getConnection() call, i.e. ? in place of your ampersand. Robert

Posted by Robert Jewett on February 27, 2008 at 09:25 PM MST #

does anyone have an idea how to do this with maven2? i have an oracle database and can´t get that problem under control so far.

Posted by niko on March 03, 2008 at 07:48 AM MST #

This worked for me. I added this to the dbunit-maven-plugin in pom.xml <url>${jdbc.url}&sessionVariables=FOREIGN_KEY_CHECKS=0</url>

Posted by Paddy Daly on May 19, 2008 at 10:27 AM MDT #

In PostgresQL you can make foreign key constraints deferable. I generate the SQL for the database setup (boctest.sql) with Hibernate Tools, and before running it I insert the required SQL code via Ant:

Works fine.

Posted by Andreas Saremba on August 13, 2008 at 03:35 AM MDT #

Why do you want to disable the foreign keys? You just need to sort/filter the queries in the order/sequence of their dependence using the following piece of code:

IDataSet fullDataSet = conn.createDataSet();
fullDataSet = new FilteredDataSet(new DatabaseSequenceFilter(conn), fullDataSet);

That is it.

NOTE: I think disabling foreign key constraints could make your tests undependable.

Cheers,
Gene

Posted by Gene on September 08, 2008 at 06:02 PM MDT #

@Gene - your solution works when you're using DbUnit from Java. However, I tend to use it from Ant and/or Maven and don't have the luxury of being able to sort the dependencies.

Posted by Matt Raible on September 09, 2008 at 09:42 AM MDT #

I am using maven dbunit plugin against the oracle database, why I always get the connection refused error after add &sessionVariables=FOREIGN_KEY_CHECKS=0 to the end of the JDBC URL?

Posted by JianXin on September 09, 2008 at 11:19 PM MDT #

@JianXin - my guess is you're getting this error because the parameters are specific to MySQL. I'm willing to bet Oracle doesn't support this (nor have a strategy for enabling ignoring of foreign keys).

Posted by 67.190.164.167 on September 09, 2008 at 11:21 PM MDT #

@Matt - I am using appFuse 2.0.2, and struggling with issues on dbunit. Should I use anything instead of dbunit? I'm not even doing circular references wth FK's, just the FK's give me a casting error. Disabling the FK by adding to the jdbc url doesn't seem to work either ...

Posted by BobbyD on September 12, 2008 at 08:23 AM MDT #

how can we use the ANT strategy that replaces the add constraints statements in a maven enviroment? I am not using net as my build tool. that sounds like a good solution for me...

Posted by Flavio on March 27, 2009 at 05:50 PM MDT #

thanks

Posted by 115.108.96.148 on July 21, 2010 at 04:55 AM MDT #

For Oracle, seems that the FEATURE_SKIP_ORACLE_RECYCLEBIN_TABLES DbUnit property works.

See here for source

Can not see it in the DbUnit documentation :(

Posted by moudug on November 18, 2011 at 07:41 AM MST #

Hi, definitely... how can disable foreign key check in sql server??

Posted by zax on January 24, 2013 at 10:04 AM MST #

10+ YEARS

TWITTER

RECENT ENTRIES

Tag Cloud

DbUnit Tip: Turn off foreign key constraints when importing into MySQL