[DERBY-6559] A immediate Fk constraint blows up iff its referenced PK is deferred and we delete a duplicate

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 10.11.1.1
• Component/s: SQL
• Labels: derby_backport_reject_10_10
• Assignee: Dag H. Wanvik
• Reporter: Dag H. Wanvik
• Created: 29/Apr/14 22:31
• Updated: 09/Sep/16 09:54
• Resolved: 09/May/14 14:59

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.

Issue Links

• breaks: DERBY-532 Support deferrable constraints
• relates to: DERBY-6576 A immediate Fk constraint blows up iff its referenced PK is deferred and we modify a duplicate key column