CODEWITHSUNDEEP
oracle-databaseconstraintsdbunit
David
David

Reputation: 21

dbunit disable Oracle constraint

I'm using DbUnit 2.4.8 with Oracle 10g and JUnit 4.5. I'd like to disable the foreign key constraints in Oracle while running the DbUnit tests I have so I can test single tables independent of all other tables. This is what I have so far:

I have created a class (DBUnitHelper) which extends DatabaseTestCase. I've 

@Override
protected IDatabaseConnection getConnection() throws Exception {

    if (usingInternalCtx_)
    {
        if (!esa.util.SysConfig.isRunning())
            esa.util.SysConfig.startupSystem();

        ctx_ = OraPool.getCtx();
    }

    //disable foreign keys
    Connection con = ctx_.getConnection();
    con.prepareStatement("SET CONSTRAINTS ALL DEFERRED").execute();

    return new OracleConnection(con, "my_schema");  // DatabaseConnection(con_);
}

The JUnit test method is:

@Test
public void useDatabaseTesterToRemoveExistingDataThenRunTest()
{
    IDataSet dataset = null;

    try
    {
        IDatabaseTester databaseTester = dbunit_.getDatabaseTester();
        databaseTester.setDataSet(dbunit_.getDataSet());
        databaseTester.onSetup();   // clean out existing entries in the table specified by the dataset and populate it with entries from the database

        IDataSet databaseDataSet = databaseTester.getDataSet();
        // IDataSet databaseDataSet = con.createDataSet();  // uncomment to retrieve actual rows from the database
        ITable actualTable = databaseDataSet.getTable(TABLE_NAME);

        // Load expected data from an XML dataset
        IDataSet expectedDataSet = dbunit_.getDataSet();
        ITable expectedTable = expectedDataSet.getTable(TABLE_NAME);

        // Assert new testing database table match expected (xml) table
        assertEquals(3,expectedTable.getRowCount());
        assertEquals(expectedTable.getRowCount(), actualTable.getRowCount());
        assertEquals(expectedTable.getValue(1, "oid"), actualTable.getValue(1, "oid"));
        Assertion.assertEquals(expectedTable, actualTable);

        databaseTester.onTearDown();  // by default does nothing
    } catch (Exception e)
    {
        e.printStackTrace();
        fail("test_names has problems");
    }
}

I get an ORA-02291: integrity constraint violated - parent key not found error on the Junit line: databaseTester.onSetup();. When I step through this with the debugger, I see that the DBUnitHelper.getConnection() is never being called.

Any ideas on what I need to fix to disable the constraints in Oracle?

Upvotes: 2

Views: 2460

Answers (1)

DCookie
DCookie

Reputation: 43533

The first thing to ask is: "Were the constraints on the tables you are working on created as DEFERRABLE?"

You can check this by issuing this query:

SELECT constraint_name, table_name, DEFERRABLE 
  FROM all_constraints 
 WHERE owner = 'myschema'
   AND table_name = 'THE_TABLE';

If the constraints are not created as DEFERRABLE, the command SET ALL CONSTRAINTS DEFERRED essentially has no effect.

If the constraints are not deferrable, you must drop and re-create them as deferrable. You can't modify them to be deferrable.

Upvotes: 3

Related Questions