db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d..@apache.org
Subject svn commit: r1590849 [3/3] - in /db/derby/code/trunk/java: engine/org/apache/derby/iapi/sql/conn/ engine/org/apache/derby/iapi/sql/dictionary/ engine/org/apache/derby/iapi/types/ engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/impl/sq...
Date Tue, 29 Apr 2014 00:23:53 GMT
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java?rev=1590849&r1=1590848&r2=1590849&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ConstraintCharacteristicsTest.java Tue Apr 29 00:23:52 2014
@@ -26,7 +26,6 @@ import java.sql.DatabaseMetaData;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.ArrayList;
@@ -63,6 +62,8 @@ public class ConstraintCharacteristicsTe
     private static final String LANG_CHECK_CONSTRAINT_VIOLATED = "23513";
     private static final String LANG_DEFERRED_CHECK_VIOLATION_T = "23514";
     private static final String LANG_DEFERRED_CHECK_VIOLATION_S = "23515";
+    private static final String LANG_DEFERRED_FK_VIOLATION_T = "23516";
+    private static final String LANG_DEFERRED_FK_VIOLATION_S = "23517";
     private static final String LOCK_TIMEOUT = "40XL1";
     private static final String LANG_INCONSISTENT_C_CHARACTERISTICS = "42X97";
     private static final String LANG_DROP_OR_ALTER_NON_EXISTING_C = "42X86";
@@ -94,12 +95,15 @@ public class ConstraintCharacteristicsTe
     public static Test suite() {
         final String nameRoot = ConstraintCharacteristicsTest.class.getName();
         final TestSuite suite = new TestSuite(nameRoot);
+
         suite.addTest(baseSuite(nameRoot + ":embedded"));
         suite.addTest(TestConfiguration.clientServerDecorator(
                 baseSuite(nameRoot + ":client")));
+
         suite.addTest(restSuite(nameRoot + ":embedded"));
         suite.addTest(TestConfiguration.clientServerDecorator(
                 restSuite(nameRoot + ":client")));
+
         return suite;
     }
 
@@ -108,6 +112,9 @@ public class ConstraintCharacteristicsTe
     private static Test restSuite(final String name) {
 
         final TestSuite suite = new TestSuite(name);
+
+        suite.addTest(new ConstraintCharacteristicsTest(
+                "testDeferredRowsInvalidation"));
         suite.addTest(new ConstraintCharacteristicsTest(
                 "testLockingForUniquePK"));
 
@@ -145,8 +152,6 @@ public class ConstraintCharacteristicsTe
         suite.addTest(new ConstraintCharacteristicsTest(
                       "testRoutines"));
         suite.addTest(new ConstraintCharacteristicsTest(
-                      "testDeferredRowsInvalidation"));
-        suite.addTest(new ConstraintCharacteristicsTest(
                       "testImport"));
         suite.addTest(new ConstraintCharacteristicsTest(
                       "testDerby6374"));
@@ -172,8 +177,9 @@ public class ConstraintCharacteristicsTe
     @Override
     protected void setUp() throws Exception {
         super.setUp();
-        createStatement().
-                executeUpdate("create table referenced(i int primary key)");
+        final Statement s = createStatement();
+        s.executeUpdate("create table referenced(" +
+                        "    i int primary key, j int default 0)");
 
         if ((usingEmbedded() && !exportFilesCreatedEmbedded) ||
             (usingDerbyNetClient() && !exportFilesCreatedClient)) {
@@ -192,7 +198,6 @@ public class ConstraintCharacteristicsTe
                 SupportFilesSetup.getReadWrite("t.data").getPath();
             expImpDataWithNullsFile =
                 SupportFilesSetup.getReadWrite("t_with_nulls.data").getPath();
-            final Statement s = createStatement();
             s.executeUpdate("create table t(i int)");
             s.executeUpdate("insert into t values 1,-2,-2, 3");
             s.executeUpdate("create table t_with_nulls(i int)");
@@ -209,6 +214,7 @@ public class ConstraintCharacteristicsTe
             s.executeUpdate("drop table t_with_nulls");
         }
 
+        s.close();
         setAutoCommit(false);
     }
 
@@ -347,8 +353,7 @@ public class ConstraintCharacteristicsTe
         }
 
         for (String[] ch : nonDefaultCharacteristics) {
-            if (ch[0].startsWith(" references") ||
-                ch[0].contains("not enforced")) {
+            if (ch[0].contains("not enforced")) {
 
                 assertStatementError(NOT_IMPLEMENTED,
                         s,
@@ -419,6 +424,10 @@ public class ConstraintCharacteristicsTe
     static final String[] checkForms = {
             "create table t(i int, j int, constraint c check (i > 0)"};
 
+    static final String[] fkForms = {
+            "create table t(i int, j int, " +
+            "    constraint c foreign key(i) references referenced(i)"};
+
     static final String[] checkSpec = { // corresponding to above forms
             "check (i > 0)"};
 
@@ -491,13 +500,7 @@ public class ConstraintCharacteristicsTe
             c2.setAutoCommit(false);
 
             final Statement s2 = c2.createStatement();
-
-            try {
-                s2.executeUpdate("insert into t1 values 4");
-                fail();
-            } catch (SQLException e) {
-                assertSQLState(LOCK_TIMEOUT, e);
-            }
+            assertStatementError(LOCK_TIMEOUT, s2, "insert into t1 values 4");
         } finally {
             if (c2 != null) {
                 c2.rollback();
@@ -546,13 +549,7 @@ public class ConstraintCharacteristicsTe
 
             final Statement s2 = c2.createStatement();
             s2.executeUpdate("insert into t2 values 4");
-
-            try {
-                c2.commit();
-                fail();
-            } catch (SQLException e) {
-                assertSQLState(LOCK_TIMEOUT, e);
-            }
+            assertCommitError(LOCK_TIMEOUT, c2);
         } finally {
             try {
                 if (c2 != null) {
@@ -678,12 +675,8 @@ public class ConstraintCharacteristicsTe
                                          sCF + " immediate");
 
                     // Now try to commit, which should lead to rollback
-                    try {
-                        commit();
-                        fail("expected duplicates error on commit");
-                    } catch (SQLException e) {
-                        assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
-                    }
+                    assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T,
+                                      getConnection());
 
                     // Verify that contents are the same as before we did the
                     // duplicate inserts
@@ -737,12 +730,8 @@ public class ConstraintCharacteristicsTe
                             {"3", "31"}});
 
                     // Now try to commit, which should lead to rollback
-                    try {
-                        commit();
-                        fail("expected duplicates error on commit");
-                    } catch (SQLException e) {
-                        assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
-                    }
+                    assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T,
+                                      getConnection());
 
                     // Verify that contents are the same as before we did the
                     // duplicate updates
@@ -791,22 +780,16 @@ public class ConstraintCharacteristicsTe
                             {"3", "31"}});
 
                     // But since we still have duplicates, the commit will fail
-                    try {
-                        commit();
-                        fail("expected duplicates error on commit");
-                    } catch (SQLException e) {
-                        assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
-                    }
+                    assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T,
+                                      getConnection());
 
                     checkConsistencyOfBaseTableAndIndex(s);
 
 
                 } finally {
                     idx++;
-                    try {
-                        s.executeUpdate("drop table t");
-                        commit();
-                    } catch (SQLException e) {}
+                    dontThrow(s, "drop table t");
+                    commit();
                 }
             }
 
@@ -864,12 +847,8 @@ public class ConstraintCharacteristicsTe
                                          sCF + " immediate");
 
                     // Now try to commit, which should lead to rollback
-                    try {
-                        commit();
-                        fail("expected duplicates error on commit");
-                    } catch (SQLException e) {
-                        assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
-                    }
+                    assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+                                      getConnection());
 
                     // Verify that contents are the same as before we did the
                     // duplicate inserts
@@ -921,12 +900,8 @@ public class ConstraintCharacteristicsTe
                             {"-4", "31"}});
 
                     // Now try to commit, which should lead to rollback
-                    try {
-                        commit();
-                        fail("expected duplicates error on commit");
-                    } catch (SQLException e) {
-                        assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
-                    }
+                    assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+                                      getConnection());
 
                     // Verify that contents are the same as before we did the
                     // duplicate inserts
@@ -971,22 +946,16 @@ public class ConstraintCharacteristicsTe
                             {"3", "30"}});
 
                     // But since we still have violations, the commit will fail
-                    try {
-                        commit();
-                        fail("expected duplicates error on commit");
-                    } catch (SQLException e) {
-                        assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
-                    }
+                    assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+                                      getConnection());
 
                     checkConsistencyOfBaseTableAndIndex(s);
 
 
                 } finally {
                     idx++;
-                    try {
-                        s.executeUpdate("drop table t");
-                        commit();
-                    } catch (SQLException e) {}
+                    dontThrow(s, "drop table t");
+                    commit();
                 }
             }
         }
@@ -1021,10 +990,7 @@ public class ConstraintCharacteristicsTe
                         s,
                         "call calledNested(false)");
             } finally {
-                try {
-                    s.executeUpdate("drop table t");
-                    commit();
-                } catch (SQLException e) {}
+                dontThrow(s, "drop table t");
             }
         }
 
@@ -1045,10 +1011,8 @@ public class ConstraintCharacteristicsTe
                             s,
                             "call calledNested(false)");
                 } finally {
-                    try {
-                        s.executeUpdate("drop table t");
-                        commit();
-                    } catch (SQLException e) {}
+                    dontThrow(s, "drop table t");
+                    commit();
                 }
             }
         }
@@ -1089,10 +1053,8 @@ public class ConstraintCharacteristicsTe
                         s,
                         "call calledNested(true)");
             } finally {
-                try {
-                    s.executeUpdate("drop table t");
-                    commit();
-                } catch (SQLException e) {}
+                dontThrow(s, "drop table t");
+                commit();
             }
         }
 
@@ -1113,23 +1075,19 @@ public class ConstraintCharacteristicsTe
                             s,
                             "call calledNested(true)");
                 } finally {
-                    try {
-                        s.executeUpdate("drop table t");
-                        commit();
-                    } catch (SQLException e) {}
+                    dontThrow(s, "drop table t");
+                    commit();
                 }
             }
         }
 
-        // Check that we don't bark if we actually introduced the duplicates
+        // Check that we don't bark if we actually introduced the violations
         // in the caller session context
         for (String ct : checkForms) {
             try {
-                s.executeUpdate(
-                        ct + " deferrable initially deferred)");
-                s.executeUpdate(
-                        "insert into t values " + rs2Values(initialContents));
-
+                s.executeUpdate(ct + " deferrable initially deferred)");
+                s.executeUpdate("insert into t values " +
+                                rs2Values(negatedInitialContents));
                 declareCalledNested(s);
                 s.executeUpdate("call calledNested(true)");
             } finally {
@@ -1146,13 +1104,98 @@ public class ConstraintCharacteristicsTe
                 s.executeUpdate(
                    "insert into t values " + rs2Values(negatedInitialContents));
                 declareCalledNestedSetImmediate(s);
-                s.executeUpdate("call calledNestedSetImmediate()");
-            } catch (SQLException e) {
-                assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_S, e);
+                assertStatementError(LANG_DEFERRED_CHECK_VIOLATION_S,
+                                     s, "call calledNestedSetImmediate()");
             } finally {
                 rollback();
             }
         }
+
+        //
+        //    F O R E I G N   K E Y   C O N S T R A I N T S
+        //
+        for (String ct : fkForms) {
+            try {
+                s.executeUpdate(
+                        ct + " deferrable initially immediate)");
+                s.executeUpdate("insert into referenced values " +
+                        rs2Values(initialContents));
+                s.executeUpdate(
+                        "insert into t values " + rs2Values(initialContents));
+                commit();
+
+                declareCalledNestedFk(s);
+                assertStatementError(
+                        LANG_DEFERRED_FK_VIOLATION_T,
+                        s,
+                        "call calledNestedFk()");
+            } finally {
+                dontThrow(s, "drop table t");
+                dontThrow(s, "delete from referenced");
+                commit();
+            }
+        }
+
+        // Constraint is initially deferred, but mode then set to immediate
+        // before the call
+        for (String setConstraintForm : setConstraintsForms) {
+            for (String ct : fkForms) {
+                try {
+                    s.executeUpdate(
+                        ct + " deferrable initially deferred)");
+                    s.executeUpdate(
+                        "insert into t values " + rs2Values(initialContents));
+                    s.executeUpdate(
+                        "insert into referenced(i) select i from t");
+                    commit();
+
+                    s.executeUpdate(setConstraintForm + " immediate");
+                    declareCalledNestedFk(s);
+                    assertStatementError(LANG_DEFERRED_FK_VIOLATION_T,
+                            s,
+                            "call calledNestedFk()");
+                } finally {
+                    dontThrow(s, "drop table t");
+                    dontThrow(s, "delete from referenced");
+                    commit();
+                }
+            }
+        }
+
+        // Check that we don't bark if we actually introduced the violations
+        // in the caller session context
+        for (String ct : fkForms) {
+            try {
+                s.executeUpdate(
+                        ct + " deferrable initially deferred)");
+                s.executeUpdate(
+                        "insert into t values " + rs2Values(initialContents));
+                declareCalledNestedFk(s);
+                s.executeUpdate("call calledNestedFk()");
+                assertCommitError(LANG_DEFERRED_FK_VIOLATION_T,
+                                  getConnection());
+            } finally {
+                rollback();
+            }
+        }
+
+        // Check what happens if routine set mode to immediate with
+        // deferred rows inserted by caller
+        for (String ct : fkForms) {
+            try {
+                s.executeUpdate(
+                   ct + " deferrable initially deferred)");
+                s.executeUpdate(
+                   "insert into t values " + rs2Values(initialContents));
+                declareCalledNestedSetImmediate(s);
+                assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+                                     "call calledNestedSetImmediate()");
+            } finally {
+                rollback();
+            }
+        }
+
+
     }
 
     public void testDeferredRowsInvalidation() throws SQLException {
@@ -1160,6 +1203,8 @@ public class ConstraintCharacteristicsTe
         //
         //   U N I Q U E,   P R I M A R Y   K E Y   C O N S T R A I N T
         //
+
+        //   D r o p   t h e   c o n s t r a i n t
         s.executeUpdate("create table t(i int, " +
                         "  constraint c primary key (i) initially deferred)");
         s.executeUpdate("insert into t values 1,2,2,3");
@@ -1174,60 +1219,224 @@ public class ConstraintCharacteristicsTe
         commit();
         s.executeUpdate("drop table t");
         commit();
+
+        //   D r o p   t h e   t a b l e
+        s.executeUpdate("create table t(i int, " +
+                        "  constraint c primary key (i) initially deferred)");
+        s.executeUpdate("insert into t values 1,2,2,3");
+        assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s,
+                             "set constraints c immediate");
+        s.executeUpdate("drop table t");
+        commit();
+
+        //   T r u n c a t e   t h e   t a b l e
+        s.executeUpdate("create table t(i int, " +
+                        "  constraint c primary key (i) initially deferred)");
+        s.executeUpdate("insert into t values 1,2,2,3");
+        assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s,
+                             "set constraints c immediate");
+        s.executeUpdate("truncate table t");
+        s.executeUpdate("set constraints c immediate");
+        s.executeUpdate("set constraints c deferred");
+        s.executeUpdate("insert into t values 1,2,2,3");
+        assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s,
+                             "set constraints c immediate");
+        s.executeUpdate("drop table t");
+        commit();
+
+        //   C o m p r e s s   t h e   t a b l e
+        s.executeUpdate("create table t(i int, " +
+                "  constraint c primary key (i) initially deferred)");
+        s.executeUpdate("insert into t values 1,2,2,3");
+        assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s,
+                             "set constraints c immediate");
+        s.executeUpdate("delete from t where i=1");
+        s.executeUpdate("call syscs_util.syscs_compress_table('APP', 'T', 0)");
+        assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s,
+                             "set constraints c immediate");
+        assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T, getConnection());
+
+        s.executeUpdate("create table t(i int, " +
+                "  constraint c primary key (i) initially deferred)");
+        commit();
+        s.executeUpdate("insert into t values 1,2,3");
+        s.executeUpdate("delete from t where i=1");
+
+        // Inline compress times out if we add a PK (even without deferred
+        // constraints)
+        assertStatementError(LOCK_TIMEOUT, s,
+                "call syscs_util.syscs_inplace_compress_table(" +
+                "'APP', 'T', 1, 1, 1)");
+        // assertStatementError(LANG_DEFERRED_DUP_VIOLATION_S, s,
+        //                      "set constraints c immediate");
+        // assertCommitError(LANG_DEFERRED_DUP_VIOLATION_T, getConnection());
+        s.executeUpdate("drop table t");
+        commit();
+
         //
         //   C H E C K   C O N S T R A I N T
         //
+
+        //   D r o p   t h e   c o n s t r a i n t
         s.executeUpdate("create table t(i int, " +
                 "  constraint c check (i > 0) initially deferred)");
         s.executeUpdate("insert into t values -1,-2, -2, -3");
+        assertStatementError(LANG_DEFERRED_CHECK_VIOLATION_S, s,
+                             "set constraints c immediate");
 
-        // drop the constraint with outstanding violations
         s.executeUpdate("alter table t drop constraint c");
         commit();
-
         s.executeUpdate("drop table t");
+        commit();
+
+        //   D r o p   t h e   t a b l e
         s.executeUpdate("create table t(i int, " +
                 "  constraint c check (i > 0) initially deferred)");
         s.executeUpdate("insert into t values -1, -2, -2, -3");
-
-        // drop the table itself with outstanding violations
+        assertStatementError(LANG_DEFERRED_CHECK_VIOLATION_S, s,
+                             "set constraints c immediate");
         s.executeUpdate("drop table t");
         commit();
 
+        //   T r u n c a t e   t h e   t a b l e
         s.executeUpdate("create table t(i int, " +
                 "  constraint c check (i > 0) initially deferred)");
         s.executeUpdate("insert into t values -1, -2, -2, -3");
-
-        // truncate the table with outstanding violations
+        assertStatementError(LANG_DEFERRED_CHECK_VIOLATION_S, s,
+                             "set constraints c immediate");
         s.executeUpdate("truncate table t");
         commit();
+        s.executeUpdate("drop table t");
+        commit();
+
+        //   C o m p r e s s   t h e   t a b l e
+        //
+        // We can no longer rely on row locations, so we do a full table scan
+        // instead to detect any violations.
+        s.executeUpdate("create table t(i int, " +
+                "  constraint c check (i > 0) initially deferred)");
 
-        // compress the table before commit: we can no longer rely
-        // on row locations, so we do a full table scan instead to detect
-        // any violations.
         s.executeUpdate("insert into t values -1, -2, -2, -3");
         s.executeUpdate("delete from t where i=-2");
         s.executeUpdate("call syscs_util.syscs_compress_table('APP', 'T', 0)");
-        try {
-            commit();
-            fail("expected check violations at commit time");
-        } catch (SQLException e) {
-            assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
-        }
+        assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T, getConnection());
 
+        s.executeUpdate("create table t(i int, " +
+                "  constraint c check (i > 0) initially deferred)");
+        commit();
         s.executeUpdate("insert into t values -1, -2, -2, -3");
         s.executeUpdate("delete from t where i=-2");
         s.executeUpdate("call syscs_util.syscs_inplace_compress_table(" +
                 "'APP', 'T', 1, 1, 1)");
-        try {
-            commit();
-            fail("expected check violations at commit time");
-        } catch (SQLException e) {
-            assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
-        }
+        assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T, getConnection());
+        s.executeUpdate("drop table t");
+        commit();
+
+
+        //
+        //   F O R E I G N   K E Y  C O N S T R A I N T
+        //
+
+        //   D r o p   t h e   c o n s t r a i n t
+        s.executeUpdate("create table t(i int, constraint c foreign key(i) " +
+                "references referenced(i) initially deferred)");
+        s.executeUpdate("insert into t values 1,2,3");
+        assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+                             "set constraints c immediate");
+        s.executeUpdate("alter table t drop constraint c");
+        commit();
+        s.executeUpdate("drop table t");
+        commit();
 
+        //   T r u n c a t e   t h e   r e f e r e n c i n g   t a b l e
+        s.executeUpdate("create table t(i int, constraint c foreign key(i) " +
+                "references referenced(i) initially deferred)");
+
+        s.executeUpdate("insert into t values 1,2,3");
+        assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+                             "set constraints c immediate");
+        s.executeUpdate("truncate table t");
+        commit();
+        s.executeUpdate("insert into t values 1,2,3");
+        assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+                             "set constraints c immediate");
+        s.executeUpdate("drop table t");
+        commit();
+
+        //   T r u n c a t e   t h e   r e f e r e n c e d   t a b l e
+        //
+        //   This is only legal if all the referencing constraints are deferred
+        //   and have ON DELETE NO ACTION.
+        //
+        s.executeUpdate(
+            "create table t(i int, constraint c foreign key(i) " +
+            "references referenced(i) on delete NO ACTION initially deferred)");
+        s.executeUpdate("insert into referenced(i) values 4,5,6");
+        s.executeUpdate("insert into t values 4,5,6");
+        s.executeUpdate("set constraints c immediate");
+        s.executeUpdate("set constraints c deferred");
+        s.executeUpdate("delete from referenced where i=4");
+        s.executeUpdate("truncate table referenced");
+        assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+                             "set constraints c immediate");
         s.executeUpdate("drop table t");
+        s.executeUpdate("truncate table referenced");
         commit();
+
+        //   C o m p r e s s   t h e   r e f e r e n c i n g   t a b l e
+
+        // Compress by recreating the conglomerate
+        s.executeUpdate("create table t(i int, constraint c foreign key(i) " +
+                "references referenced(i) initially deferred)");
+        s.executeUpdate("insert into referenced(i) values 4,5,6");
+        s.executeUpdate("insert into t values 4,5,6,7");
+        s.executeUpdate("delete from t where i=5");
+        s.executeUpdate("call syscs_util.syscs_compress_table('APP', 'T', 0)");
+        assertCommitError(LANG_DEFERRED_FK_VIOLATION_T, getConnection());
+
+        // In-place compress
+        s.executeUpdate("create table t(i int, constraint c foreign key(i) " +
+                "references referenced(i) initially deferred)");
+        s.executeUpdate("insert into referenced(i) values 4,5,6");
+        s.executeUpdate("insert into t values 4,5,6,7");
+        s.executeUpdate("delete from t where i=5");
+        // s.executeUpdate("call syscs_util.syscs_inplace_compress_table(" +
+        //                 "    'APP', 'T', 1,1,1)");
+        assertStatementError(
+            LOCK_TIMEOUT, s,
+            "call syscs_util.syscs_inplace_compress_table('APP', 'T', 1,1,1)");
+        // assertCommitError(LANG_DEFERRED_FK_VIOLATION_T, getConnection());
+
+
+        //   C o m p r e s s   t h e   r e f e r e n c e d   t a b l e
+        //
+        // Compress by recreating the conglomerate
+        s.executeUpdate(
+            "create table t(i int, constraint c foreign key(i) " +
+            "references referenced(i) ON DELETE NO ACTION initially deferred)");
+        s.executeUpdate("insert into referenced(i) values 4,5,6");
+        s.executeUpdate("insert into t values 4,5,6");
+        s.executeUpdate("delete from referenced where i=5");
+        assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+                             "set constraints c immediate");
+        s.executeUpdate("call syscs_util.syscs_compress_table('APP', 'T', 0)");
+        assertCommitError(LANG_DEFERRED_FK_VIOLATION_T, getConnection());
+
+        // In-place compress
+        s.executeUpdate(
+            "create table t(i int, constraint c foreign key(i) " +
+            "references referenced(i) ON DELETE NO ACTION initially deferred)");
+        s.executeUpdate("insert into referenced(i) values 4,5,6");
+        s.executeUpdate("insert into t values 4,5,6");
+        s.executeUpdate("delete from referenced where i=5");
+        assertStatementError(LANG_DEFERRED_FK_VIOLATION_S, s,
+                             "set constraints c immediate");
+        // s.executeUpdate("call syscs_util.syscs_inplace_compress_table(" +
+        //                 "    'APP', 'T', 1,1,1)");
+        assertStatementError(
+           LOCK_TIMEOUT, s,
+           "call syscs_util.syscs_inplace_compress_table('APP', 'T', 1, 1, 1)");
+        // assertCommitError(LANG_DEFERRED_FK_VIOLATION_T, getConnection());
     }
 
     /**
@@ -1264,16 +1473,12 @@ public class ConstraintCharacteristicsTe
 
                 s.executeUpdate("set constraints c deferred");
 
-                try {
-                    // import and implicit commit leads to checking
-                    s.executeUpdate(
-                            "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
-                            "    'APP' , 'T' , '" + expImpDataFile + "'," +
-                            "    null, null , null, " + addOrReplace + ")");
-                    fail("expected duplicates error on commit");
-                } catch (SQLException e) {
-                    assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
-                }
+                // import and implicit commit leads to checking
+                assertStatementError(
+                    LANG_DEFERRED_DUP_VIOLATION_T, s,
+                    "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+                    "    'APP' , 'T' , '" + expImpDataFile + "'," +
+                    "    null, null , null, " + addOrReplace + ")");
 
                 //
                 //   U N I Q U E   N O T   N U L L   C O N S T R A I N T
@@ -1288,16 +1493,12 @@ public class ConstraintCharacteristicsTe
 
                 s.executeUpdate("set constraints c deferred");
 
-                try {
-                    // import and implicit commit leads to checking
-                    s.executeUpdate(
-                            "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
-                            "    'APP' , 'T' , '" + expImpDataFile + "'," +
-                            "    null, null , null, " + addOrReplace + ")");
-                    fail("expected duplicates error on commit");
-                } catch (SQLException e) {
-                    assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
-                }
+                // import and implicit commit leads to checking
+                assertStatementError(
+                    LANG_DEFERRED_DUP_VIOLATION_T, s,
+                    "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+                    "    'APP' , 'T' , '" + expImpDataFile + "'," +
+                    "    null, null , null, " + addOrReplace + ")");
 
                 //
                 //   n u l l a b l e   U N I Q U E   C O N S T R A I N T
@@ -1309,16 +1510,12 @@ public class ConstraintCharacteristicsTe
                     "add constraint c unique(i) initially deferred");
                 commit();
 
-                try {
-                    // import and implicit commit leads to checking
-                    s.executeUpdate(
-                            "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
-                            "    'APP' , 'T' , '" + expImpDataFile + "'," +
-                            "    null, null , null, " + addOrReplace + ")");
-                    fail("expected duplicates error on commit");
-                } catch (SQLException e) {
-                    assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
-                }
+                // import and implicit commit leads to checking
+                assertStatementError(
+                    LANG_DEFERRED_DUP_VIOLATION_T, s,
+                    "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+                    "    'APP' , 'T' , '" + expImpDataFile + "'," +
+                    "    null, null , null, " + addOrReplace + ")");
 
                 // Import OK data with multiple NULLs should still work with
                 // nullable UNIQUE deferred constraint
@@ -1338,27 +1535,19 @@ public class ConstraintCharacteristicsTe
                     "alter table t " +
                     "add constraint c check (i > 0) initially deferred");
 
-                try {
-                    // import and implicit commit leads to checking
-                    s.executeUpdate(
-                            "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
-                            "    'APP' , 'T' , '" + expImpDataFile + "'," +
-                            "    null, null , null, " + addOrReplace + ")");
-                    fail("expected check violations error on commit");
-                } catch (SQLException e) {
-                    assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
-                }
+                // import and implicit commit leads to checking
+                assertStatementError(
+                    LANG_DEFERRED_CHECK_VIOLATION_T, s,
+                    "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+                    "    'APP' , 'T' , '" + expImpDataFile + "'," +
+                    "    null, null , null, " + addOrReplace + ")");
 
                 s.executeUpdate("truncate table t");
                 commit();
             }
         } finally {
-            try {
-                s.executeUpdate("drop table t");
-                commit();
-            } catch (SQLException e) {
-                e.printStackTrace(System.out);
-            }
+            dontThrow(s, "drop table t");
+            commit();
         }
     }
 
@@ -1380,12 +1569,9 @@ public class ConstraintCharacteristicsTe
 
         s.executeUpdate("insert into constraintest(val1) values 'name1'");
 
-        try {
-            s.executeUpdate("insert into constraintest(val1) values 'name1'");
-            fail("expected duplicate error");
-        } catch (SQLException e) {
-            assertSQLState(LANG_DUPLICATE_KEY_CONSTRAINT, e);
-        }
+        assertStatementError(
+            LANG_DUPLICATE_KEY_CONSTRAINT, s,
+            "insert into constraintest(val1) values 'name1'");
 
         final PreparedStatement ps = prepareStatement(
                 "insert into constraintest(val1) values (?)");
@@ -1411,24 +1597,17 @@ public class ConstraintCharacteristicsTe
                 s.executeUpdate("alter table t add constraint c unique(i)");
                 commit();
 
-                try {
-                    s.executeUpdate(
-                            "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
-                            "    'APP' , 'T' , '" + expImpDataFile + "'," +
-                            "    null, null , null, " + addOrReplace + ")");
-                    fail("expected duplicates error on commit");
-                } catch (SQLException e) {
-                    assertSQLState(LANG_DUPLICATE_KEY_CONSTRAINT, e);
-                }
+                assertStatementError(
+                    LANG_DUPLICATE_KEY_CONSTRAINT, s,
+                    "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+                    "    'APP' , 'T' , '" + expImpDataFile + "'," +
+                    "    null, null , null, " + addOrReplace + ")");
+
                 s.executeUpdate("alter table t drop constraint c");
             }
         } finally {
-            try {
-                s.executeUpdate("drop table t");
-                commit();
-            } catch (SQLException e) {
-                e.printStackTrace(System.out);
-            }
+            dontThrow(s, "drop table t");
+            commit();
         }
     }
 
@@ -1588,6 +1767,7 @@ public class ConstraintCharacteristicsTe
                 ps.executeUpdate();
             }
             c.commit();
+            fail();
         } catch (SQLException e) {
             assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
             s.executeUpdate("call syscs_util.syscs_checkpoint_database()");
@@ -1641,10 +1821,8 @@ public class ConstraintCharacteristicsTe
         } catch (SQLException e) {
             assertSQLState(LANG_DEFERRED_DUP_VIOLATION_T, e);
         } finally {
-            try {
-                s.executeUpdate("drop table t");
-                commit();
-            } catch (SQLException e) {}
+            dontThrow(s, "drop table t");
+            commit();
         }
     }
 
@@ -1775,13 +1953,8 @@ public class ConstraintCharacteristicsTe
             s.executeUpdate("insert into tab1 values (4)");
             s.executeUpdate("insert into tab1 values (3)");
             s.executeUpdate("insert into tab1 select c1-3 from tab1");
-            try {
-                commit();
-                fail("expected " + LANG_DEFERRED_CHECK_VIOLATION_T +
-                     " on commit");
-            } catch (SQLException e) {
-                assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
-            }
+            assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+                              getConnection());
 
             //
             // U P D A T E,   D E F E R R E D   P R O C E S S I N G
@@ -1792,13 +1965,8 @@ public class ConstraintCharacteristicsTe
             s.executeUpdate("update tab1 as grr set c1=-1 where c1 = 2 and " +
                     "((select max(c1) from tab1 where grr.c1 > 0) > 0)");
 
-            try {
-                commit();
-                fail("expected " + LANG_DEFERRED_CHECK_VIOLATION_T +
-                     " on commit");
-            } catch (SQLException e) {
-                assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
-            }
+            assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+                              getConnection());
 
             // Correlated query to force deferred update processing but with
             // trigger which causes another code path.
@@ -1809,23 +1977,12 @@ public class ConstraintCharacteristicsTe
             s.executeUpdate("update tab1 as grr set c1=-1 where c1 = 2 and " +
                     "((select max(c1) from tab1 where grr.c1 > 0) > 0)");
 
-            try {
-                commit();
-                fail("expected " + LANG_DEFERRED_CHECK_VIOLATION_T +
-                     " on commit");
-            } catch (SQLException e) {
-                assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
-            }
+            assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+                              getConnection());
         } finally {
             // clean up
-            try {
-                s.executeUpdate("drop table tab1");
-            } catch (SQLException e) {}
-
-            try {
-                s.executeUpdate("drop table trigtab");
-            } catch (SQLException e) {}
-
+            dontThrow(s, "drop table tab1");
+            dontThrow(s, "drop table trigtab");
             commit();
         }
     }
@@ -1899,19 +2056,12 @@ public class ConstraintCharacteristicsTe
             s.executeUpdate("insert into t values (-1,  2,  3)");
             s.executeUpdate("update t set i=-1");
             s.executeUpdate("update t set i=-1");
-            try {
-                commit();
-                fail("expected check violation");
-            } catch (SQLException e) {
-                assertSQLState(LANG_DEFERRED_CHECK_VIOLATION_T, e);
-            }
-
+            assertCommitError(LANG_DEFERRED_CHECK_VIOLATION_T,
+                              getConnection());
 
         } finally {
-            try {
-                s.executeUpdate("drop table t");
-                commit();
-            } catch(SQLException e) {}
+            dontThrow(s, "drop table t");
+            commit();
         }
     }
 
@@ -2150,8 +2300,7 @@ public class ConstraintCharacteristicsTe
         for (String ct : tableConstraintTypes) {
             for (String[] ch : nonDefaultCharacteristics) {
                 // Only primary key and unique implemented
-                if (ct.contains("references") ||
-                    ch[0].contains("not enforced")) {
+                if (ch[0].contains("not enforced")) {
 
                     assertStatementError(NOT_IMPLEMENTED,
                             s,
@@ -2179,8 +2328,7 @@ public class ConstraintCharacteristicsTe
         for (String ct : columnConstraintTypes) {
             for (String[] ch : nonDefaultCharacteristics) {
                 // Only primary key and unique implemented
-                if (ct.startsWith(" references") ||
-                    ch[0].contains("not enforced")) {
+                if (ch[0].contains("not enforced")) {
 
                     assertStatementError(NOT_IMPLEMENTED,
                             s,
@@ -2272,8 +2420,7 @@ public class ConstraintCharacteristicsTe
                     rollback();
                 }
             } catch (SQLException e) {
-                if (characteristics.contains("not enforced") ||
-                    ct.contains("references") ) {
+                if (characteristics.contains("not enforced")) {
                     assertSQLState(NOT_IMPLEMENTED, e);
                 } else {
                     throw e;
@@ -2306,13 +2453,8 @@ public class ConstraintCharacteristicsTe
                             "    where constraintname = 'C'"),
                     new String[][]{{newState}});
         } else {
-            try {
-                s.executeUpdate("alter table t alter constraint c " +
-                    enforcement);
-                fail();
-            } catch (SQLException e) {
-                assertSQLState(NOT_IMPLEMENTED, e);
-            }
+            assertStatementError(NOT_IMPLEMENTED, s,
+                    "alter table t alter constraint c " + enforcement);
         }
     }
 
@@ -2365,22 +2507,6 @@ public class ConstraintCharacteristicsTe
         }
     }
 
-    private static void dumpFullResultSet(
-        final ResultSet rs) throws SQLException {
-
-        final ResultSetMetaData rsmd = rs.getMetaData();
-
-        while (rs.next()) {
-            for (int i = 0; i < rsmd.getColumnCount(); i++) {
-                System.out.print(rs.getString(i + 1) + " ");
-            }
-            System.out.println("");
-        }
-
-        rs.close();
-    }
-
-
     private void declareCalledNested(final Statement s) throws SQLException {
         s.executeUpdate(
             "create procedure calledNested(isCheckConstraint boolean)" +
@@ -2390,6 +2516,14 @@ public class ConstraintCharacteristicsTe
             ".calledNested' modifies sql data");
     }
 
+    private void declareCalledNestedFk(final Statement s) throws SQLException {
+        s.executeUpdate(
+            "create procedure calledNestedFk()" +
+            "  language java parameter style java" +
+            "  external name '" +
+            this.getClass().getName() +
+            ".calledNestedFk' modifies sql data");
+    }
     private void declareCalledNestedSetImmediate(final Statement s) 
             throws SQLException {
         s.executeUpdate(
@@ -2415,6 +2549,17 @@ public class ConstraintCharacteristicsTe
         c.close();
     }
 
+    public static void calledNestedFk() throws SQLException
+    {
+        final Connection c =
+            DriverManager.getConnection("jdbc:default:connection");
+        final Statement cStmt = c.createStatement();
+
+        cStmt.executeUpdate("set constraints c deferred");
+        cStmt.executeUpdate("insert into t select i*2, j*2 from t");
+        c.close();
+    }
+
     public static void calledNestedSetImmediate() throws SQLException
     {
         final Connection c =
@@ -2427,5 +2572,14 @@ public class ConstraintCharacteristicsTe
             c.close();
         }
     }
+
+    private void dontThrow(Statement st, String stm) {
+        try {
+            st.executeUpdate(stm);
+        } catch (SQLException e) {
+            // ignore, best effort here
+            println("\"" + stm+ "\"failed: " + e);
+        }
+    }
 }
 

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java?rev=1590849&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/ForeignKeysDeferrableTest.java Tue Apr 29 00:23:52 2014
@@ -0,0 +1,811 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.functionTests.tests.lang.ForeignKeysDeferrableTest
+
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements.  See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License.  You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.SQLException;
+import java.sql.Statement;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import static org.apache.derbyTesting.junit.BaseJDBCTestCase.usingDerbyNetClient;
+import static org.apache.derbyTesting.junit.BaseJDBCTestCase.usingEmbedded;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.SupportFilesSetup;
+import static org.apache.derbyTesting.junit.TestConfiguration.clientServerSuite;
+import static org.apache.derbyTesting.junit.TestConfiguration.embeddedSuite;
+
+/**
+ * Test deferrable foreign key constraints.
+ *
+ * NOTE: In contrast to constraint checking, the <em>referential actions</em>
+ * specified by a referential constraint are never deferred.
+ * (SQL 2011: section 4.18.2).
+ * <p/>
+ * Section 4.18.3.3:
+ * An {@code <update rule>} that does not contain NO ACTION specifies a
+ * referential update action. A {@code <delete rule>} that does not specify NO
+ * ACTION specifies a referential delete action. Referential update
+ * actions and referential delete actions are collectively called
+ * referential actions. Referential actions are carried out before, and
+ * are not part of, the checking of a referential constraint. Deferring a
+ * referential constraint defers the checking of the {@code <search condition>}
+ * of the constraint (a {@code <match predicate>}) but does not defer the
+ * referential actions of the referential constraint.
+ * <p/>
+ * NOTE 52 — For example, if a referential update action such as ON UPDATE
+ * CASCADE is specified, then any UPDATE operation on the referenced table will
+ * be cascaded to the referencing table as part of the UPDATE operation, even
+ * if the referential constraint is deferred. Consequently, the referential
+ * constraint cannot become violated by the UPDATE statement. On the other
+ * hand, ON UPDATE SET DEFAULT could result in a violation of the referential
+ * constraint if there is no matching row after the referencing column is set
+ * to its default value. In addition, INSERT and UPDATE operations on the
+ * referencing table do not entail any automatic enforcement of the referential
+ * constraint. Any such violations of the constraint will be detected when the
+ * referential constraint is eventually checked, at or before a commit.
+ * <p/>
+ * NOTE 53 — Even if constraint checking is not deferred, ON UPDATE
+ * RESTRICT is a stricter condition than ON UPDATE NO ACTION. ON UPDATE
+ * RESTRICT prohibits an update to a particular row if there are any
+ * matching rows; ON UPDATE NO ACTION does not perform its constraint
+ * NOTE 54 - Ditto for DELETE.
+ * <p/>
+ * Line numbers in the comments refer to svn revision 1580845 of Derby trunk.
+ */
+public class ForeignKeysDeferrableTest extends BaseJDBCTestCase
+{
+    private static final String  LANG_DEFERRED_FK_CONSTRAINT_T = "23516";
+    private static final String  LANG_DEFERRED_FK_CONSTRAINT_S = "23517";
+    private static final String  LANG_ADD_FK_CONSTRAINT_VIOLATION = "X0Y45";
+    private static final String  LANG_FK_VIOLATION = "23503";
+
+
+    private static String expImpDataFile;  // file used to perform
+                                           // import/export
+    private static boolean exportFilesCreatedEmbedded = false;
+    private static boolean exportFilesCreatedClient = false;
+
+
+    public ForeignKeysDeferrableTest(String name) {
+        super(name);
+    }
+
+
+    public static Test suite() {
+        TestSuite suite = new TestSuite();
+        suite.addTest(new SupportFilesSetup(
+                embeddedSuite(ForeignKeysDeferrableTest.class)));
+        suite.addTest(new SupportFilesSetup(
+                clientServerSuite(ForeignKeysDeferrableTest.class)));
+
+        return suite;
+    }
+
+    @Override
+    protected void setUp() throws Exception {
+        super.setUp();
+
+        Statement s = createStatement();
+
+        // <table_name> ::= <role>_<statementType>_<action>
+        // where:
+        // <role> ::=  "ref"   /* a referenced table */
+        //           | "t"     /* a referencing table: carries the FK */
+        //
+        // <statement type> ::= "d"  /* delete */
+        //                    | "u"  /* update */
+        //
+        // <action> ::= "r"  /* restrict */
+        //            | "c"  /* cascade */
+        //            | "na" /* no action */
+        //            | "nu" /* set null */
+        //
+        // Insert statements borrow the "d" tables.  The constraint names use
+        // similar semantics, e.g. "c_d_c": constraint, delete table set,
+        // cascade.
+
+        s.executeUpdate(
+                "create table ref_d_r(i int primary key, j int default 2)");
+        s.executeUpdate(
+                "create table ref_d_c(i int primary key, j int default 2)");
+        s.executeUpdate(
+                "create table ref_d_na(i int primary key, j int default 2)");
+        s.executeUpdate(
+                "create table ref_d_nu(i int primary key, j int default 2)");
+
+        s.executeUpdate(
+                "create table ref_u_r(i int primary key, j int default 2)");
+        s.executeUpdate(
+                "create table ref_u_na(i int primary key, j int default 2)");
+
+        s.executeUpdate("insert into ref_d_r values (1, default)");
+        s.executeUpdate("insert into ref_d_c values (1, default)");
+        s.executeUpdate("insert into ref_d_na values (1, default)");
+        s.executeUpdate("insert into ref_d_nu values (1, default)");
+
+        s.executeUpdate("insert into ref_u_r values (1, default)");
+        s.executeUpdate("insert into ref_u_na values (1, default)");
+
+
+        // Tables for testing delete with {CASCADE, RESTRICT, SET NULL,
+        // NOACTION}
+        s.executeUpdate(
+            "create table t_d_r(i int, j int default 2," +
+            "   constraint c_d_r foreign key (i) references ref_d_r(i) " +
+            "   on delete restrict " +
+            "deferrable initially immediate)");
+
+        s.executeUpdate(
+            "create table t_d_c(i int, j int default 2," +
+            "   constraint c_d_c foreign key (i) references ref_d_c(i) " +
+            "   on delete cascade " +
+            "deferrable initially immediate)");
+
+        s.executeUpdate(
+            "create table t_d_na(i int, j int default 2," +
+            "   constraint c_d_na foreign key (i) references ref_d_na(i) " +
+            "   on delete no action " +
+            "deferrable initially immediate)");
+
+        s.executeUpdate(
+            "create table t_d_nu(i int, j int default 2," +
+            "   constraint c_d_nu foreign key (i) references ref_d_nu(i) " +
+            "   on delete set null " +
+            "deferrable initially immediate)");
+
+        // Tables for testing update with {RESTRICT, NOACTION}
+        s.executeUpdate(
+            "create table t_u_r(i int, j int default 2," +
+            "   constraint c_u_r foreign key (i) references ref_u_r(i) " +
+            "   on update restrict " +
+            "deferrable initially immediate)");
+
+        s.executeUpdate(
+            "create table t_u_na(i int, j int default 2," +
+            "   constraint c_u_na foreign key (i) references ref_u_na(i) " +
+            "   on update no action " +
+            "deferrable initially immediate)");
+
+        s.executeUpdate("insert into t_d_r   values (1, default)");
+        s.executeUpdate("insert into t_d_c   values (1, default)");
+        s.executeUpdate("insert into t_d_na  values (1, default)");
+        s.executeUpdate("insert into t_d_nu  values (1, default)");
+        s.executeUpdate("insert into t_u_r   values (1, default)");
+        s.executeUpdate("insert into t_u_na  values (1, default)");
+
+        if ((usingEmbedded() && !exportFilesCreatedEmbedded) ||
+            (usingDerbyNetClient() && !exportFilesCreatedClient)) {
+
+            // We have to do this once for embedded and once for client/server
+            if (usingEmbedded()) {
+                exportFilesCreatedEmbedded = true;
+            } else {
+                exportFilesCreatedClient = true;
+            }
+
+            // Create a file for import that contains rows for which the
+            // foreign key constraint doesn't hold.
+            expImpDataFile =
+                SupportFilesSetup.getReadWrite("t.data").getPath();
+            s.executeUpdate("create table t(i int, j int)");
+            s.executeUpdate("insert into t values (1,2),(2,2)");
+            s.executeUpdate(
+                "call SYSCS_UTIL.SYSCS_EXPORT_TABLE (" +
+                "    'APP' , 'T' , '" + expImpDataFile + "'," +
+                "    null, null , null)");
+            s.executeUpdate("drop table t");
+        }
+
+        setAutoCommit(false);
+    }
+
+    @Override
+    protected void tearDown() throws Exception {
+        rollback();
+        setAutoCommit(true);
+        Statement s = createStatement();
+        dontThrow(s, "drop table t_d_r   ");
+        dontThrow(s, "drop table t_d_c   ");
+        dontThrow(s, "drop table t_d_na  ");
+        dontThrow(s, "drop table t_d_nu  ");
+        dontThrow(s, "drop table t_u_r   ");
+        dontThrow(s, "drop table t_u_na  ");
+
+        dontThrow(s, "drop table ref_d_r");
+        dontThrow(s, "drop table ref_d_c");
+        dontThrow(s, "drop table ref_d_na");
+        dontThrow(s, "drop table ref_d_nu");
+
+        dontThrow(s, "drop table ref_u_r");
+        dontThrow(s, "drop table ref_u_na");
+
+        super.tearDown();
+    }
+
+    /**
+     * 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);
+
+        s.executeUpdate("set constraints c_d_r deferred");
+        s.executeUpdate(DIRECT_INSERT_SQL);
+
+        assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+
+        // 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");
+
+        s.executeUpdate("delete from t_d_r where i=3");
+        commit();
+    }
+
+    /**
+     * Insert row in 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 testInsertDeferred() throws SQLException {
+        Statement s = createStatement();
+        final String DEFERRED_INSERT_SQL =
+                "insert into t_d_r select i+1,j from t_d_r";
+
+        // ...ForeignKeyRIChecker.doCheck(ForeignKeyRIChecker.java:99)
+        // ...GenericRIChecker.doCheck(GenericRIChecker.java:91)
+        // ...RISetChecker.doFKCheck(RISetChecker.java:121)
+        // ...InsertResultSet.normalInsertCore(InsertResultSet.java:1205)
+        // ...InsertResultSet.open(InsertResultSet.java:497)
+
+        assertStatementError(LANG_FK_VIOLATION, s, DEFERRED_INSERT_SQL);
+
+        s.executeUpdate("set constraints c_d_r deferred");
+        s.executeUpdate(DEFERRED_INSERT_SQL);
+
+        assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+
+        // 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(DEFERRED_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(DEFERRED_INSERT_SQL);
+
+        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");
+
+        s.executeUpdate("delete from t_d_r where i=3");
+        commit();
+    }
+
+    /**
+     * Update row in non-deferred code path. Note that this use of "deferred"
+     * refers to the insert processing, not the deferrable constraint.
+     *
+     * @throws SQLException
+     */
+    public void testUpdateDirect() throws SQLException {
+        Statement s = createStatement();
+
+        // Child:
+
+        final String RESTRICTED_UPDATE_CHILD_SQL =
+            "update t_u_r set i=2 where j=2";
+
+        final String NO_ACTION_UPDATE_CHILD_SQL =
+            "update t_u_na set i=2 where j=2";
+
+        // NO ACTION: This should be deferred in deferred mode
+        // ...ForeignKeyRIChecker.doCheck(ForeignKeyRIChecker.java:99)
+        // ...GenericRIChecker.doCheck(GenericRIChecker.java:91)
+        // ...RISetChecker.doFKCheck(RISetChecker.java:121)
+        // ...UpdateResultSet.collectAffectedRows(UpdateResultSet.java:614)
+        // ...UpdateResultSet.open(UpdateResultSet.java:259)
+
+        //   N O   A C T I O N,     R E S T R I C T
+        // Both are treated as NO ACTION since we are updating the child, not
+        // the parent here.
+        String[] constraint = new String[]{"c_u_r", "c_u_na"};
+        String[] sql = new String[]{RESTRICTED_UPDATE_CHILD_SQL,
+                                    NO_ACTION_UPDATE_CHILD_SQL};
+
+        for (int i = 0; i < 2; i++) {
+            assertStatementError(LANG_FK_VIOLATION, s, sql[i]);
+            final String setDeferred =
+                    "set constraints " + constraint[i] + " deferred";
+            final String setImmediate =
+                    "set constraints " + constraint[i] + " immediate";
+            s.executeUpdate(setDeferred);
+            s.executeUpdate(sql[i]);
+            assertStatementError(
+                    LANG_DEFERRED_FK_CONSTRAINT_S, s, setImmediate);
+            assertStatementError(
+                    LANG_DEFERRED_FK_CONSTRAINT_S, s, setImmediate);
+            assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+        }
+
+        //   C A S C A D E,    S E T   N U L L
+        //   Not applicable for update (Derby restriction). But if they had been
+        //   implemented, the behavior should be as for NO ACTION above.
+
+        // Parent:
+
+        // Always performed deferred update mode, cf. this this explanation in
+        // TableDescriptor#getAllRelevantConstraints: "For update, if we are
+        // updating a referenced key, then we have to do it in deferred mode
+        // (in case we update multiple rows)".
+    }
+
+    /**
+     * Update row in deferred code path. Note that this use of "deferred"
+     * refers to the insert processing, not the deferrable constraint.
+     *
+     * @throws SQLException
+     */
+    public void testUpdateDeferred() throws SQLException {
+        Statement s = createStatement();
+
+        // Update child
+
+        final String RESTRICTED_UPDATE_CHILD_SQL =
+            "update t_u_r set i=2 where i=1";
+
+        final String NO_ACTION_UPDATE_CHILD_SQL =
+            "update t_u_na set i=2 where i=1";
+
+        final String RESTRICTED_UPDATE_PARENT_SQL =
+            "update ref_u_r set i = 2 where i = 1";
+
+        final String NO_ACTION_UPDATE_PARENT_SQL =
+            "update ref_u_na set i = 2 where i = 1";
+
+        // RESTRICT and NO ACTION. Since we are updating the child, we
+        // run implicitly in NO ACTION mode, so can be deferred
+        //
+        // ...ForeignKeyRIChecker.doCheck(ForeignKeyRIChecker.java:99)
+        // ...RISetChecker.doRICheck(RISetChecker.java:151)
+        // ...UpdateResultSet.runChecker(UpdateResultSet.java:1005)
+        // ...UpdateResultSet.open(UpdateResultSet.java:274)
+
+        //   N O   A C T I O N,     R E S T R I C T
+        // Both are treated as NO ACTION since we are updating the child, not
+        // the parent here.
+
+        String[] constraint = new String[]{"c_u_r", "c_u_na"};
+        String[] sql = new String[]{RESTRICTED_UPDATE_CHILD_SQL,
+                                    NO_ACTION_UPDATE_CHILD_SQL};
+
+        for (int i = 0; i < 2; i++) {
+            assertStatementError(LANG_FK_VIOLATION, s, sql[i]);
+            final String setDeferred =
+                    "set constraints " + constraint[i] + " deferred";
+            final String setImmediate =
+                    "set constraints " + constraint[i] + " immediate";
+            s.executeUpdate(setDeferred);
+            s.executeUpdate(sql[i]);
+            assertStatementError(
+                    LANG_DEFERRED_FK_CONSTRAINT_S, s, setImmediate);
+            assertStatementError(
+                    LANG_DEFERRED_FK_CONSTRAINT_S, s, setImmediate);
+            assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+        }
+
+
+
+        // Parent, RESTRICT Should *never* be deferred.
+        // ...ReferencedKeyRIChecker.doCheck(ReferencedKeyRIChecker.java:108)
+        // ...RISetChecker.doRICheck(RISetChecker.java:151)
+        // ...UpdateResultSet.runChecker(UpdateResultSet.java:961)
+        // ...UpdateResultSet.open(UpdateResultSet.java:269)
+        assertStatementError(LANG_FK_VIOLATION, s,
+                             RESTRICTED_UPDATE_PARENT_SQL);
+
+        // Since the action is RESTRICT, deferred constraint doesn't help:
+        s.executeUpdate("set constraints c_u_r deferred");
+        assertStatementError(LANG_FK_VIOLATION, s,
+                             RESTRICTED_UPDATE_PARENT_SQL);
+
+
+        // Parent, NO ACTION (different code path, cf. line 269 vs 274).
+        // Should be deferred if constraint mode is deferred.
+        //
+        // ...ReferencedKeyRIChecker.doCheck(ReferencedKeyRIChecker.java:108)
+        // ...RISetChecker.doRICheck(RISetChecker.java:151)
+        // ...UpdateResultSet.runChecker(UpdateResultSet.java:961)
+        // ...UpdateResultSet.open(UpdateResultSet.java:274) <-- Note:difference
+        assertStatementError(LANG_FK_VIOLATION, s, NO_ACTION_UPDATE_PARENT_SQL);
+
+        // Since the action is NO ACTION, deferral should work
+        s.executeUpdate("set constraints c_u_na deferred");
+        s.executeUpdate(NO_ACTION_UPDATE_PARENT_SQL);
+        assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+                             "set constraints c_u_na immediate");
+        assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+                             "set constraints c_u_na immediate");
+        assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+
+        // Resolve by resetting the value in the referenced row
+        s.executeUpdate("set constraints c_u_na deferred");
+        s.executeUpdate(NO_ACTION_UPDATE_PARENT_SQL);
+        s.executeUpdate("update ref_u_na set i=1 where i=2");
+        commit();
+
+        // Resolve by resetting the referencing row
+        s.executeUpdate("set constraints c_u_na deferred");
+        s.executeUpdate(NO_ACTION_UPDATE_CHILD_SQL);
+        assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+                             "set constraints c_u_na immediate");
+        s.executeUpdate("update t_u_na set i=1 where i=2");
+        commit();
+
+    }
+
+    /**
+     * Delete row in non-deferred code path. Note that this use of "deferred"
+     * refers to the insert processing, not the deferrable constraint.
+     *
+     * @throws SQLException
+     */
+    public void testDeleteDirect() throws SQLException {
+        Statement s = createStatement();
+
+        // Delete of child row is trivial, parent no affected.
+
+        // Parent
+
+        final String RESTRICTED_DELETE_SQL = "delete from ref_d_r where i = 1";
+        final String NO_ACTION_DELETE_SQL = "delete from ref_d_na where i = 1";
+        final String CASCADE_DELETE_SQL = "delete from ref_d_c where i = 1";
+        final String SET_NULL_DELETE_SQL = "delete from ref_d_nu where i = 1";
+
+        // RESTRICT and NO ACTION: As far as triggers, there is no difference
+        // when the execution of checking happens here, since in the presence
+        // of triggers, row processing is deferred. But for deferred constraints
+        // we need to treat these two differently: The RESTRICT code path
+        // should check even in the presence of deferred FK constraints,
+        // the NO ACTION code path should wait.
+        //
+        // ...ReferencedKeyRIChecker.doCheck(ReferencedKeyRIChecker.java:108)
+        // ...RISetChecker.doPKCheck(RISetChecker.java:97)
+        // ...DeleteResultSet.collectAffectedRows(DeleteResultSet.java:392)
+        // ...DeleteResultSet.open(DeleteResultSet.java:136)
+
+        //   R E S T R I C T
+        assertStatementError(LANG_FK_VIOLATION, s, RESTRICTED_DELETE_SQL);
+
+        // Since the action is RESTRICT, deferred constraint doesn't help.
+        s.executeUpdate("set constraints c_d_r deferred");
+        assertStatementError(LANG_FK_VIOLATION, s, RESTRICTED_DELETE_SQL);
+
+        //   N O   A C T I O N
+        assertStatementError(LANG_FK_VIOLATION, s, NO_ACTION_DELETE_SQL);
+
+        // Since the action is NO ACTION, deferral should work
+        s.executeUpdate("set constraints c_d_na deferred");
+        s.executeUpdate(NO_ACTION_DELETE_SQL);
+        assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+                             "set constraints c_d_na immediate");
+        assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+                             "set constraints c_d_na immediate");
+        assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+
+        // Resolve by removing the referencing row
+        s.executeUpdate("set constraints c_d_na deferred");
+        s.executeUpdate(NO_ACTION_DELETE_SQL);
+        s.executeUpdate("delete from t_d_na where i=1");
+        commit();
+
+        // Resolve by re-inserting the referenced row
+        s.executeUpdate("insert into ref_d_na values (1, default)");
+        s.executeUpdate("insert into t_d_na values (1, default)");
+        commit();
+        s.executeUpdate("set constraints c_d_na deferred");
+        s.executeUpdate(NO_ACTION_DELETE_SQL);
+        assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+                             "set constraints c_d_na immediate");
+        s.executeUpdate("insert into ref_d_na values (1, default)");
+        commit();
+
+        //   C A S C A D E : not impacted by deferred
+        s.executeUpdate(CASCADE_DELETE_SQL);
+        JDBC.assertEmpty(s.executeQuery("select * from t_d_c"));
+        rollback();
+
+        s.executeUpdate("set constraints c_d_c deferred");
+        s.executeUpdate(CASCADE_DELETE_SQL);
+        JDBC.assertEmpty(s.executeQuery("select * from t_d_c"));
+        commit();
+
+        //   S E T   N U L L : not impacted by deferred
+        s.executeUpdate(SET_NULL_DELETE_SQL);
+        assertResults(
+                s.executeQuery("select i from t_d_nu"),
+                new String[][]{{null}},
+                false);
+        rollback();
+
+        s.executeUpdate("set constraints c_d_nu deferred");
+        s.executeUpdate(SET_NULL_DELETE_SQL);
+        assertResults(
+                s.executeQuery("select i from t_d_nu"),
+                new String[][]{{null}},
+                false);
+        commit();
+    }
+
+    /**
+     * Delete row in deferred code path. Note that this use of "deferred"
+     * refers to the insert processing, not the deferrable constraint.
+     *
+     * @throws SQLException
+     */
+        public void testDeleteDeferred() throws SQLException {
+        Statement s = createStatement();
+        // Delete of child row is trivial, parent no affected.
+
+        // Parent
+
+        final String RESTRICTED_DELETE_SQL =
+                "delete from ref_d_r where i = 1 and " +
+                "    i in (select i from ref_d_r)";
+
+        final String NO_ACTION_DELETE_SQL =
+                "delete from ref_d_na where i = 1 and " +
+                "    i in (select i from ref_d_na)";
+
+        final String CASCADE_DELETE_SQL =
+                "delete from ref_d_c where i = 1 and " +
+                "    i in (select i from ref_d_c)";
+
+        final String SET_NULL_DELETE_SQL =
+                "delete from ref_d_nu where i = 1 and " +
+                "    i in (select i from ref_d_nu)";
+
+
+        // RESTRICT - This checking should *never* be deferred
+        // ...ReferencedKeyRIChecker.doCheck(ReferencedKeyRIChecker.java:108)
+        // ...RISetChecker.doPKCheck(RISetChecker.java:97)
+        // ...DeleteResultSet.runFkChecker(DeleteResultSet.java:559)
+        // ...DeleteResultSet.open(DeleteResultSet.java:151)
+
+        //   R E S T R I C T
+        assertStatementError(LANG_FK_VIOLATION, s, RESTRICTED_DELETE_SQL);
+
+        // Since the action is RESTRICT, deferred constraint doesn't help.
+        s.executeUpdate("set constraints c_d_r deferred");
+        assertStatementError(LANG_FK_VIOLATION, s, RESTRICTED_DELETE_SQL);
+
+        // NO ACTION - This checking should be deferred in deferred mode
+        // ...ReferencedKeyRIChecker.doCheck(ReferencedKeyRIChecker.java:108)
+        // ...RISetChecker.doPKCheck(RISetChecker.java:97)
+        // ...DeleteResultSet.runFkChecker(DeleteResultSet.java:559)
+        // ...DeleteResultSet.open(DeleteResultSet.java:154) <-- Note:difference
+
+        //   N O   A C T I O N
+        assertStatementError(LANG_FK_VIOLATION, s, NO_ACTION_DELETE_SQL);
+
+        // Since the action is NO ACTION, deferral should work
+        s.executeUpdate("set constraints c_d_na deferred");
+        s.executeUpdate(NO_ACTION_DELETE_SQL);
+        assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+                             "set constraints c_d_na immediate");
+        assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+                             "set constraints c_d_na immediate");
+        assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+
+        // Resolve by removing the referencing row
+        s.executeUpdate("set constraints c_d_na deferred");
+        s.executeUpdate(NO_ACTION_DELETE_SQL);
+        s.executeUpdate("delete from t_d_na where i=1");
+        commit();
+
+        // Resolve by re-inserting the referenced row
+        s.executeUpdate("insert into ref_d_na values (1, default)");
+        s.executeUpdate("insert into t_d_na values (1, default)");
+        commit();
+        s.executeUpdate("set constraints c_d_na deferred");
+        s.executeUpdate(NO_ACTION_DELETE_SQL);
+        assertStatementError(LANG_DEFERRED_FK_CONSTRAINT_S, s,
+                             "set constraints c_d_na immediate");
+        s.executeUpdate("insert into ref_d_na values (1, default)");
+        commit();
+
+        //   C A S C A D E : not impacted by deferred
+        s.executeUpdate(CASCADE_DELETE_SQL);
+        JDBC.assertEmpty(s.executeQuery("select * from t_d_c"));
+        rollback();
+
+        s.executeUpdate("set constraints c_d_c deferred");
+        s.executeUpdate(CASCADE_DELETE_SQL);
+        JDBC.assertEmpty(s.executeQuery("select * from t_d_c"));
+        commit();
+
+        //   S E T   N U L L : not impacted by deferred
+        s.executeUpdate(SET_NULL_DELETE_SQL);
+        assertResults(
+                s.executeQuery("select i from t_d_nu"),
+                new String[][]{{null}},
+                false);
+        rollback();
+
+        s.executeUpdate("set constraints c_d_nu deferred");
+        s.executeUpdate(SET_NULL_DELETE_SQL);
+        assertResults(
+                s.executeQuery("select i from t_d_nu"),
+                new String[][]{{null}},
+                false);
+        commit();
+    }
+
+    /**
+     * Insert using bulk import code path, i.e. IMPORT. Since IMPORT
+     * always performs a commit at the end, we strictly do no need to do
+     * extra processing for deferrable constraints, but we do so
+     * anyway to prepare for possible future lifting of this restriction to
+     * IMPORT. This behavior can no be observed externally, but we include
+     * the test here anyway as a baseline.
+     *
+     * @throws SQLException
+     */
+    public void testBulkInsert() throws SQLException {
+        Statement s = createStatement();
+
+        // Try the test cases below with both "replace" and "append"
+        // semantics. 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.
+        for (int addOrReplace = 0; addOrReplace < 2; addOrReplace++) {
+            // import and implicit commit leads to checking
+
+            // ADD:
+            // ...ForeignKeyRIChecker.doCheck(ForeignKeyRIChecker.java:99)
+            // ...GenericRIChecker.doCheck(GenericRIChecker.java:91)
+            // ...RISetChecker.doFKCheck(RISetChecker.java:121)
+            // ...InsertResultSet.normalInsertCore(InsertResultSet.java:1028)
+            // ...InsertResultSet.open(InsertResultSet.java:497)
+
+            // REPLACE:
+            // ...InsertResultSet.bulkValidateForeignKeysCore(
+            //         InsertResultSet.java:1726)
+            // ...InsertResultSet.bulkValidateForeignKeys(
+            //         InsertResultSet.java:1594)
+            // ...InsertResultSet.open(InsertResultSet.java:490)
+
+            assertStatementError(
+                LANG_FK_VIOLATION,
+                s,
+                "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+                "    'APP' , 'T_D_R' , '" + expImpDataFile + "'," +
+                "    null, null , null, " + addOrReplace + ")");
+
+            s.executeUpdate("set constraints c_d_r deferred");
+            assertStatementError(
+                LANG_DEFERRED_FK_CONSTRAINT_T,
+                s,
+                "call SYSCS_UTIL.SYSCS_IMPORT_TABLE (" +
+                "    'APP' , 'T_D_R' , '" + expImpDataFile + "'," +
+                "    null, null , null, " + addOrReplace + ")");
+        }
+    }
+
+    public void testAddConstraint() throws SQLException {
+        Statement s = createStatement();
+        s.executeUpdate("create table t2(i int)");
+
+        try {
+            s.executeUpdate("insert into t2 values 1,2");
+            commit();
+
+            // First, try with immediate checking
+            assertStatementError(LANG_ADD_FK_CONSTRAINT_VIOLATION, s,
+                    "alter table t2 add constraint " +
+                    "    c2 foreign key(i) references ref_d_r(i)");
+            s.executeUpdate("delete from t2 where i=2");
+
+            // Delete the row with 2 should make it OK to add the constraint:
+            s.executeUpdate("alter table t2 add constraint " +
+                    "c2 foreign key(i) references ref_d_r(i)");
+
+            rollback();
+            s.executeUpdate("delete from t2");
+
+            // Now try with deferred constraint
+            s.executeUpdate("insert into t2 values 1,2");
+            commit();
+
+            s.executeUpdate("alter table t2 add constraint " +
+                    "    c2 foreign key(i) references ref_d_r(i) " +
+                    "    initially deferred");
+
+            assertCommitError(LANG_DEFERRED_FK_CONSTRAINT_T, getConnection());
+
+
+        } finally {
+            dontThrow(s, "drop table t2");
+            commit();
+        }
+    }
+
+    private void dontThrow(Statement st, String stm) {
+        try {
+            st.executeUpdate(stm);
+        } catch (SQLException e) {
+            // ignore, best effort here
+            println("\"" + stm+ "\"failed: " + e);
+        }
+    }
+}

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=1590849&r1=1590848&r2=1590849&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Tue Apr 29 00:23:52 2014
@@ -254,6 +254,7 @@ public class _Suite extends BaseTestCase
         suite.addTest(CurrentSchemaTest.suite());
         suite.addTest(Test_6496.suite());
         suite.addTest(ConstraintCharacteristicsTest.suite());
+        suite.addTest(ForeignKeysDeferrableTest.suite());
         suite.addTest(DB2IsolationLevelsTest.suite());
         suite.addTest(LuceneSuite.suite());
         suite.addTest(ConsistencyCheckerTest.suite());

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java?rev=1590849&r1=1590848&r2=1590849&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/upgradeTests/Changes10_11.java Tue Apr 29 00:23:52 2014
@@ -304,9 +304,7 @@ public class Changes10_11 extends Upgrad
             "create table t532(i int not null primary key deferrable)",
             "create table t532(i int unique deferrable)",
             "create table t532(i int not null unique deferrable)",
-            "create table t532(i int check (i > 0) deferrable)"};
-
-        String[] cDeferrableColNotYet = new String[]{
+            "create table t532(i int check (i > 0) deferrable)",
             "create table t532(i int references referenced(i) deferrable)"};
 
         String[] cDeferrableTab = new String[]{
@@ -314,9 +312,7 @@ public class Changes10_11 extends Upgrad
             "create table t532(i int, constraint c unique(i) deferrable)",
             "create table t532(i int not null, constraint c unique(i) " + 
                 "deferrable)",
-            "create table t532(i int, constraint c check (i > 0) deferrable)"};
-        
-        String[] cDeferrableTabNotYet = new String[]{
+            "create table t532(i int, constraint c check (i > 0) deferrable)",
             "create table t532(i int, constraint c foreign key(i) " + 
                 "references referenced(i) deferrable)"};
 
@@ -331,11 +327,6 @@ public class Changes10_11 extends Upgrad
                     assertStatementError(SYNTAX_ERROR, st, s);
                     assertStatementError(SYNTAX_ERROR, st, s);
                 }
-                
-                for (String s : cDeferrableColNotYet) {
-                    assertStatementError(SYNTAX_ERROR, st, s);
-                    assertStatementError(SYNTAX_ERROR, st, s);
-                }
                 break;
                 
             case PH_POST_SOFT_UPGRADE:
@@ -343,11 +334,6 @@ public class Changes10_11 extends Upgrad
                     assertStatementError(SYNTAX_ERROR, st, s);
                     assertStatementError(SYNTAX_ERROR, st, s);
                 }
-
-                for (String s : cDeferrableColNotYet) {
-                    assertStatementError(SYNTAX_ERROR, st, s);
-                    assertStatementError(SYNTAX_ERROR, st, s);
-                }
                 break;
                 
             case PH_SOFT_UPGRADE:
@@ -355,11 +341,6 @@ public class Changes10_11 extends Upgrad
                     assertStatementError(HARD_UPGRADE_REQUIRED, st, s);
                     assertStatementError(HARD_UPGRADE_REQUIRED, st, s);
                 }
-                
-                for (String s : cDeferrableColNotYet) {
-                    assertStatementError(HARD_UPGRADE_REQUIRED, st, s);
-                    assertStatementError(HARD_UPGRADE_REQUIRED, st, s);
-                }
                 break;
                 
             case PH_HARD_UPGRADE:
@@ -369,12 +350,6 @@ public class Changes10_11 extends Upgrad
                     st.execute(s);
                     rollback();
                 }
-
-                for (String s : cDeferrableColNotYet) {
-                    assertStatementError(NOT_IMPLEMENTED, st, s);
-                    assertStatementError(NOT_IMPLEMENTED, st, s);
-                }
-                
                 break;
             }
         } finally {

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java?rev=1590849&r1=1590848&r2=1590849&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/junit/BaseJDBCTestCase.java Tue Apr 29 00:23:52 2014
@@ -1228,7 +1228,19 @@ public abstract class BaseJDBCTestCase
         assertStatementError(new String[] {sqlState},st,query);
     }
         
-   
+    /**
+     * Assert that that a commit fails with the given error
+     * @param sqlState state
+     * @param c        the connection
+     */
+    public static void assertCommitError(String sqlState, Connection c) {
+        try {
+            c.commit();
+            fail();
+        } catch (SQLException e) {
+            assertSQLState(sqlState, e);
+        }
+    }
    
     /**
      * Assert that the query fails (either in compilation,
@@ -1660,7 +1672,7 @@ public abstract class BaseJDBCTestCase
         s.close();
     }
 
-    protected static void dumpRs(ResultSet s) throws SQLException {
+    public static void dumpRs(ResultSet s) throws SQLException {
         dumpRs(s, System.out);
     }
 
@@ -1697,7 +1709,7 @@ public abstract class BaseJDBCTestCase
      * Assert that the statement returns the correct results.
      */
     protected void assertResults( Connection conn, String query, String[][] rows, boolean trimResults )
-        throws Exception
+        throws SQLException
     {
         PreparedStatement   ps = chattyPrepare( conn, query );
         ResultSet                   rs = ps.executeQuery();
@@ -1712,7 +1724,7 @@ public abstract class BaseJDBCTestCase
      * Assert that the ResultSet returns the desired rows.
      */
     protected void assertResults( ResultSet rs, String[][] rows, boolean trimResults )
-        throws Exception
+        throws SQLException
     {
         int     rowCount = rows.length;
 



Mime
View raw message