Examples with DerbySQLIntegrityConstraintViolationException org.apache.derby.shared.common.error.DerbySQLIntegrityConstraintViolationException used on opensource projects

Example 1 with DerbySQLIntegrityConstraintViolationException

use of org.apache.derby.shared.common.error.DerbySQLIntegrityConstraintViolationException in project derby by apache.

the class ConstraintCharacteristicsTest method testBasicDeferral.

public void testBasicDeferral() throws SQLException {
    final Statement s = createStatement();
    for (String sCF : setConstraintsForms) {
        int idx = 0;
        // 
        for (String ct : uniqueForms) {
            try {
                s.executeUpdate(ct + " deferrable initially immediate)");
                s.executeUpdate("insert into t values " + rs2Values(initialContents));
                commit();
                // 
                // I N S E R T   O F   D U P L I C A T E S
                // 
                // Normal duplicate insert should fail, still
                // immediate mode
                assertStatementError(LANG_DUPLICATE_KEY_CONSTRAINT, s, "insert into t values (2,30)");
                // Now set deferred mode in one of two ways: by specifying
                // ALL or by naming our index explicitly.
                s.executeUpdate(sCF + " deferred");
                // Duplicate insert should now work
                s.executeUpdate("insert into t values (2,19),(2,21),(3,31)");
                // Check contents
                JDBC.assertFullResultSet(s.executeQuery("select * from t"), new String[][] { { "1", "10" }, { "2", "20" }, { "3", "30" }, { "2", "19" }, { "2", "21" }, { "3", "31" } });
                // Check contents: specify ORDER BY and force use of index
                // use the index.
                JDBC.assertFullResultSet(s.executeQuery("select * from t --DERBY-PROPERTIES constraint=c\n" + "     order by i"), new String[][] { { "1", "10" }, { "2", "20" }, { "2", "19" }, { "2", "21" }, { "3", "30" }, { "3", "31" } });
                // Try to set immediate mode, and detect violation
                assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s, sCF + " immediate");
                // Once more, error above should not roll back
                assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s, sCF + " immediate");
                // Now try to commit, which should lead to rollback
                assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T, getConnection());
                // Verify that contents are the same as before we did the
                // duplicate inserts
                JDBC.assertFullResultSet(s.executeQuery("select * from t"), initialContents);
                // Setting immediate now should work again:
                s.executeUpdate(sCF + " immediate");
                assertStatementError(LANG_DUPLICATE_KEY_CONSTRAINT, s, "insert into t values (2,30)");
                // setting deferred again:
                s.executeUpdate(sCF + " deferred");
                // Duplicate insert should now work
                s.executeUpdate("insert into t values (2,19),(2,21),(3,31)");
                assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s, sCF + " immediate");
                rollback();
                // 
                // U P D A T E   G I V I N G   D U P L I C A T E S
                // 
                // Now set deferred mode in one of two ways: by specifying
                // ALL or by naming our constraint explicitly.
                s.executeUpdate(sCF + " deferred");
                // Now test the same, but using UPDATE instead of INSERT
                s.executeUpdate("insert into t values (20,19),(200,21),(30,31)");
                s.executeUpdate("update t set i=2 where i=20");
                s.executeUpdate("update t set i=2 where i=200");
                s.executeUpdate("update t set i=3 where i=30");
                // Check result: specify ORDER BY and force use of index
                // use the index
                JDBC.assertFullResultSet(s.executeQuery("select * from t --DERBY-PROPERTIES constraint=c\n" + "     order by i"), new String[][] { { "1", "10" }, { "2", "20" }, { "2", "19" }, { "2", "21" }, { "3", "30" }, { "3", "31" } });
                // Now try to commit, which should lead to rollback
                assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T, getConnection());
                // Verify that contents are the same as before we did the
                // duplicate updates
                JDBC.assertFullResultSet(s.executeQuery("select * from t"), initialContents);
                // Specify ORDER BY and force use of index use the index
                JDBC.assertFullResultSet(s.executeQuery("select * from t --DERBY-PROPERTIES constraint=c\n" + "     order by i"), initialContents);
                checkConsistencyOfBaseTableAndIndex(s);
                // Test add of a deferred constraint to an existing table
                s.execute("alter table t drop constraint c");
                // Insert duplicates: no constraint now
                s.executeUpdate("insert into t values (2,19),(2,21),(3,31)");
                commit();
                // We can't add a constraint with immediate checking
                // because of the existing duplicates.
                assertStatementError(LANG_DUPLICATE_KEY_CONSTRAINT, s, "alter table t add constraint c " + uniqueSpec[idx]);
                // But we can add a deferred constraint:
                s.executeUpdate("alter table t add constraint c " + uniqueSpec[idx] + " deferrable initially deferred");
                // Specify ORDER BY and force use of index use the index
                JDBC.assertFullResultSet(s.executeQuery("select * from t --DERBY-PROPERTIES constraint=c\n" + "     order by i"), new String[][] { { "1", "10" }, { "2", "20" }, { "2", "19" }, { "2", "21" }, { "3", "30" }, { "3", "31" } });
                // But since we still have duplicates, the commit will fail
                assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T, getConnection());
                checkConsistencyOfBaseTableAndIndex(s);
            } finally {
                idx++;
                dropTable("t");
                commit();
            }
        }
        // 
        // C H E C K   C O N S T R A I N T S
        // 
        idx = 0;
        for (String ct : checkForms) {
            try {
                s.executeUpdate(ct + " deferrable initially immediate)");
                s.executeUpdate("insert into t values " + rs2Values(initialContents));
                commit();
                // 
                // I N S E R T   O F   V I O L A T I N G   R O W S
                // 
                // Normal duplicate insert should fail, still
                // immediate mode
                assertStatementError(LANG_CHECK_CONSTRAINT_VIOLATED, s, "insert into t values (-2,30)");
                // Test the DERBY-6773 support:
                try {
                    s.execute("insert into t values (-2,30)");
                    fail();
                } catch (DerbySQLIntegrityConstraintViolationException dsicve) {
                    assertSQLState(LANG_CHECK_CONSTRAINT_VIOLATED, dsicve);
                    assertEquals("\"APP\".\"T\"", dsicve.getTableName());
                    assertEquals("C", dsicve.getConstraintName());
                }
                // Now set deferred mode in one of two ways: by specifying
                // ALL or by naming our index explicitly.
                s.executeUpdate(sCF + " deferred");
                // Rows violating CHECK constraint should now work
                s.executeUpdate("insert into t values (-2,30),(1,31),(-3,32)");
                // Check contents
                JDBC.assertFullResultSet(s.executeQuery("select * from t"), new String[][] { { "1", "10" }, { "2", "20" }, { "3", "30" }, { "-2", "30" }, { "1", "31" }, { "-3", "32" } });
                // Try to set immediate mode, and detect violation
                assertStatementError(LANG_DEFERRED_CHECK_VIOLATION_S, s, sCF + " immediate");
                // Once more, error above should not roll back
                assertStatementError(LANG_DEFERRED_CHECK_VIOLATION_S, s, sCF + " immediate");
                // Test the DERBY-6773 support:
                try {
                    s.execute(sCF + " immediate");
                    fail();
                } catch (DerbySQLIntegrityConstraintViolationException dsicve) {
                    assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_S, dsicve);
                    assertEquals("\"APP\".\"T\"", dsicve.getTableName());
                    assertEquals("C", dsicve.getConstraintName());
                }
                // Test the DERBY-6773 support:
                try {
                    getConnection().commit();
                    fail();
                } catch (DerbySQLIntegrityConstraintViolationException dsicve) {
                    assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, dsicve);
                    assertEquals("\"APP\".\"T\"", dsicve.getTableName());
                    assertEquals("C", dsicve.getConstraintName());
                }
                // Verify that contents are the same as before we did the
                // duplicate inserts
                JDBC.assertFullResultSet(s.executeQuery("select * from t"), initialContents);
                // Setting immediate now should work again:
                s.executeUpdate(sCF + " immediate");
                assertStatementError(LANG_CHECK_CONSTRAINT_VIOLATED, s, "insert into t values (-2,30)");
                // setting deferred again:
                s.executeUpdate(sCF + " deferred");
                // Insert with check violations should now work
                s.executeUpdate("insert into t values (-2,19),(2,21),(-3,31)");
                assertStatementError(LANG_DEFERRED_CHECK_VIOLATION_S, s, sCF + " immediate");
                rollback();
                // 
                // U P D A T E   G I V I N G   V I O L A T I N G   R O W S
                // 
                // Now set deferred mode in one of two ways: by specifying
                // ALL or by naming our constraint explicitly.
                s.executeUpdate(sCF + " deferred");
                // Now test the same, but using UPDATE instead of INSERT
                s.executeUpdate("insert into t values (20,19),(200,21),(30,31)");
                s.executeUpdate("update t set i=-2 where i=20");
                s.executeUpdate("update t set i=-3 where i=200");
                s.executeUpdate("update t set i=-4 where i=30");
                // Check result
                JDBC.assertFullResultSet(s.executeQuery("select * from t order by j"), new String[][] { { "1", "10" }, { "-2", "19" }, { "2", "20" }, { "-3", "21" }, { "3", "30" }, { "-4", "31" } });
                // Now try to commit, which should lead to rollback
                assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T, getConnection());
                // Verify that contents are the same as before we did the
                // duplicate inserts
                JDBC.assertFullResultSet(s.executeQuery("select * from t"), initialContents);
                JDBC.assertFullResultSet(s.executeQuery("select * from t order by i"), initialContents);
                checkConsistencyOfBaseTableAndIndex(s);
                // Test add of a deferred constraint to an existing table
                s.execute("alter table t drop constraint c");
                // Insert "violating" rows: no constraint now
                s.executeUpdate("insert into t values (-2,19),(2,21),(-3,31)");
                commit();
                // We can't add a constraint with immediate checking
                // because of the existing violations..
                assertStatementError(LANG_ADD_CHECK_CONSTRAINT_FAILED, s, "alter table t add constraint c " + checkSpec[idx]);
                // But we can add a deferred constraint:
                s.executeUpdate("alter table t add constraint c " + checkSpec[idx] + " deferrable initially deferred");
                JDBC.assertFullResultSet(s.executeQuery("select * from t order by i,j"), new String[][] { { "-3", "31" }, { "-2", "19" }, { "1", "10" }, { "2", "20" }, { "2", "21" }, { "3", "30" } });
                // But since we still have violations, the commit will fail
                assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T, getConnection());
                checkConsistencyOfBaseTableAndIndex(s);
            } finally {
                idx++;
                dropTable("t");
                commit();
            }
        }
    }
}

Example 2 with DerbySQLIntegrityConstraintViolationException

use of org.apache.derby.shared.common.error.DerbySQLIntegrityConstraintViolationException in project derby by apache.

the class ForeignKeysDeferrableTest method testSharedConglomerates.

/**
 * Regression test case for DERBY-6665. The prevention of shared physical
 * conglomerates in the presence of the deferrable constraint
 * characteristic failed for foreign keys sometimes.  This sometimes made
 * the deferred check of constraints miss violations if two deferred
 * constraints erroneously shared a physical conglomerate.
 */
public void testSharedConglomerates() throws SQLException {
    setAutoCommit(false);
    Statement s = createStatement();
    s.execute("create table d6665_t1(x int primary key)");
    s.execute("create table d6665_t2(x int primary key)");
    // Create a table with two foreign keys - they would share one
    // conglomerate since they are declared on the same column and the hole
    // in the sharing avoidance logic.
    s.execute("create table d6665_t3(x int " + "references d6665_t1 initially deferred " + "references d6665_t2 initially deferred)");
    s.execute("insert into d6665_t1 values 1");
    // This violates the second foreign key, since T2 doesn't contain 1.
    // No error here since the constraint is deferred.
    s.execute("insert into d6665_t3 values 1");
    // Now we're no longer violating the foreign key.
    s.execute("insert into d6665_t2 values 1");
    // Introduce a violation of the first foreign key. No error because
    // the checking is deferred.
    s.execute("delete from d6665_t1");
    // Commit. Should fail because of the violation introduced by the
    // delete statement above. Was not detected before DERBY-6665.
    assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
    // Another example: A PRIMARY KEY constraint and a FOREIGN KEY
    // constraint erroneously share a conglomerate.
    s.execute("create table d6665_t4(x int primary key)");
    s.execute("create table d6665_t5(x int " + "primary key initially deferred " + "references d6665_t4 initially deferred)");
    // First violate the foreign key. No error, since it is deferred.
    s.execute("insert into d6665_t5 values 1");
    // No longer in violation of the foreign key after this statement.
    s.execute("insert into d6665_t4 values 1");
    // Violate the PRIMARY KEY constraint on T5.X.
    s.execute("insert into d6665_t5 values 1");
    // Test the DERBY-6773 support, too.
    try {
        getConnection().commit();
        fail();
    } catch (DerbySQLIntegrityConstraintViolationException dsicve) {
        assertSQLState(LANG_DEFERRED_DUPLICATE_KEY_CONSTRAINT_T, dsicve);
        assertEquals("D6665_T5", dsicve.getTableName());
        assertTrue(dsicve.getConstraintName().startsWith("SQL"));
    }
}

Example 3 with DerbySQLIntegrityConstraintViolationException

use of org.apache.derby.shared.common.error.DerbySQLIntegrityConstraintViolationException in project derby by apache.

the class ForeignKeysDeferrableTest method testInsertDirect.

/**
 * Insert row in non-deferred code path. Note that this use of "deferred"
 * refers to the insert processing, not the deferrable constraint.
 * It doesn't matter what constraint action we have on a FK when inserting,
 * it is always in "NO ACTION" mode, i.e. the constraint can be
 * deferred. In this example, we use the {t,ref}_d_r table pair, but it
 * could have been any of the others.
 *
 * @throws SQLException
 */
public void testInsertDirect() throws SQLException {
    Statement s = createStatement();
    final String DIRECT_INSERT_SQL = "insert into t_d_r values (2, default)";
    // ...ForeignKeyRIChecker.doCheck(ForeignKeyRIChecker.java:99)
    // ...GenericRIChecker.doCheck(GenericRIChecker.java:91)
    // ...RISetChecker.doFKCheck(RISetChecker.java:121)
    // ...InsertResultSet.normalInsertCore(InsertResultSet.java:1028)
    assertStatementError(LANG_FK_VIOLATION, s, DIRECT_INSERT_SQL);
    // Test the DERBY-6773 support:
    try {
        s.execute(DIRECT_INSERT_SQL);
        fail();
    } catch (DerbySQLIntegrityConstraintViolationException dsicve) {
        assertSQLState(LANG_FK_VIOLATION, dsicve);
        assertEquals("T_D_R", dsicve.getTableName());
        assertEquals("C_D_R", dsicve.getConstraintName());
    }
    s.executeUpdate("set constraints c_d_r deferred");
    s.executeUpdate(DIRECT_INSERT_SQL);
    // to exercise the DERBY-6773 support:
    try {
        getConnection().commit();
        fail();
    } catch (DerbySQLIntegrityConstraintViolationException dsicve) {
        assertSQLState(LANG_DEFERRED_FK_CONSTRAINT_T, dsicve);
        assertEquals("\"APP\".\"T_D_R\"", dsicve.getTableName());
        assertEquals("C_D_R", dsicve.getConstraintName());
    }
    // Now see deferred check succeed by actually adding referenced key
    // *after* the insert of the referencing row. Also check that setting
    // immediate constraint mode throws a statement level error.
    s.executeUpdate("set constraints c_d_r deferred");
    s.executeUpdate(DIRECT_INSERT_SQL);
    assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s, "set constraints c_d_r immediate");
    s.executeUpdate("insert into ref_d_r values (2, default)");
    commit();
    // Now see deferred check of we after inserting the referencing row
    // delete it again before commit. Also check that setting immediate
    // constraint mode throws a statement level error.
    s.executeUpdate("set constraints c_d_r deferred");
    s.executeUpdate(DIRECT_INSERT_SQL.replaceAll("2", "3"));
    assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s, "set constraints c_d_r immediate");
    assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s, "set constraints c_d_r immediate");
    // Test the DERBY-6773 support:
    try {
        s.execute("set constraints c_d_r immediate");
        fail();
    } catch (DerbySQLIntegrityConstraintViolationException dsicve) {
        assertSQLState(LANG_DEFERRED_FK_CONSTRAINT_S, dsicve);
        assertEquals("\"APP\".\"T_D_R\"", dsicve.getTableName());
        assertEquals("C_D_R", dsicve.getConstraintName());
    }
    s.executeUpdate("delete from t_d_r where i=3");
    commit();
}

Example 4 with DerbySQLIntegrityConstraintViolationException

use of org.apache.derby.shared.common.error.DerbySQLIntegrityConstraintViolationException in project derby by apache.

the class ConstraintCharacteristicsTest method testCompressTableOKUnique.

// Exposed a bug when running regression suites with default
// deferrable: compress recreates the index.
public void testCompressTableOKUnique() throws SQLException {
    final Statement stmt = createStatement();
    stmt.executeUpdate("create table table1(" + "name1 int unique deferrable initially immediate, " + "name2 int unique not null, " + "name3 int primary key)");
    try {
        stmt.execute("call syscs_util.syscs_compress_table('APP','TABLE1',1)");
        stmt.executeUpdate("insert into table1 values(1,11,111)");
        // The following should run into problem because of constraint
        // on name1
        assertStatementError(LANG_DUPLICATE_KEY_CONSTRAINT, stmt, "insert into table1 values(1,22,222)");
        // The following should run into problem because of constraint
        // on name2
        assertStatementError(LANG_DUPLICATE_KEY_CONSTRAINT, stmt, "insert into table1 values(3,11,333)");
        // The following should run into problem because of constraint
        // on name3
        assertStatementError(LANG_DUPLICATE_KEY_CONSTRAINT, stmt, "insert into table1 values(4,44,111)");
        // Test the DERBY-6773 support:
        try {
            stmt.execute("insert into table1 values(1,22,222)");
            fail();
        } catch (DerbySQLIntegrityConstraintViolationException dsicve) {
            assertSQLState(LANG_DUPLICATE_KEY_CONSTRAINT, dsicve);
            assertEquals("TABLE1", dsicve.getTableName());
            assertTrue(dsicve.getConstraintName().startsWith("SQL"));
        }
    } finally {
        stmt.executeUpdate("drop table table1");
    }
}

Example 5 with DerbySQLIntegrityConstraintViolationException

use of org.apache.derby.shared.common.error.DerbySQLIntegrityConstraintViolationException in project derby by apache.

the class ConstraintCharacteristicsTest method testDerby6773.

/**
 * DERBY-6773: check the Derby-specific subclass of the standard
 * SQLIntegrityConstraintViolationException.
 */
public void testDerby6773() throws Exception {
    final Statement s = createStatement();
    s.executeUpdate("create table Application " + "   (id bigint generated by default as identity," + "    name varchar(255)," + "    shortName varchar(32)," + "    userId varchar(32)," + "    primary key (id))");
    s.executeUpdate("create unique index UK_APPLICATION_SHORTNAME " + "    on Application (shortName)");
    s.executeUpdate("create unique index UK_APPLICATION_NAME " + "    on Application (name)");
    s.executeUpdate("insert into Application (name, shortName, userId) " + "    VALUES ('fooApp', 'Foo Application 0', 'me')");
    try {
        s.executeUpdate("insert into Application (name, shortName, userId) " + "    VALUES ('fooApp', 'Foo Application 1', 'me')");
        fail();
    } catch (DerbySQLIntegrityConstraintViolationException dsicve) {
        assertEquals("APPLICATION", dsicve.getTableName());
        assertEquals("UK_APPLICATION_NAME", dsicve.getConstraintName());
    }
    try {
        s.executeUpdate("insert into Application (name, shortName, userId) " + "    VALUES ('BarApp', 'Foo Application 0', 'me')");
        fail();
    } catch (DerbySQLIntegrityConstraintViolationException dsicve) {
        assertEquals("APPLICATION", dsicve.getTableName());
        assertEquals("UK_APPLICATION_SHORTNAME", dsicve.getConstraintName());
    }
}