Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6559

A immediate Fk constraint blows up iff its referenced PK is deferred and we delete a duplicate

    XMLWordPrintableJSON

    Details

      Description

      Cf the following test case:

      testFKPlusUnique
          /**
           * The referenced constraint (in the referenced table) is also a deferred
           * (unique/ok) constraint.
           * 
           * @throws SQLException 
           */
          public void testFKPlusUnique() throws SQLException {
              Statement s = createStatement(
                      ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
              
              try {
                  s.executeUpdate(
                      "create table ref_t(i int, " +
                      "    constraint ct primary key(i) deferrable initially deferred)");
                  s.executeUpdate(
                      "create table t(i int unique not null, " +
                      "    constraint c foreign key (i) references ref_t(i) " +
                      "    deferrable initially immediate)");
                  
                  s.executeUpdate("insert into ref_t values 1,1");
                  s.executeUpdate("insert into t values 1");
                  
                  // Now, the child (referencing table) is referencing one of the the
                  // rows whose value is 1, so the reference is potentially suspect.
                  
                  // What happens when we delete the one copy before commit?
                  ResultSet rs = s.executeQuery("select * from ref_t");
                  rs.next();
                  
                  // Will this delete blow up? Hopefully not, here is another row
                  // that would satisfy the constraint.
                  rs.deleteRow();
                  
                  // Now there should be only one left, so the referenced table is
                  // OK.
                  commit();
                  :
      

      Now, the constraint C throws when we do the "rs.deleteRow" above. But since there is (still) a row satisfying the FK, albeit a duplicate, I believe it should not.

        Attachments

        1. derby-6559.diff
          16 kB
          Dag H. Wanvik
        2. derby-6559.status
          1 kB
          Dag H. Wanvik

          Issue Links

            Activity

              People

              • Assignee:
                dagw Dag H. Wanvik
                Reporter:
                dagw Dag H. Wanvik
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: