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

["Dag H. Wanvik (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-6559?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13984925#comment-13984925 ] 

Dag H. Wanvik commented on DERBY-6559:
--------------------------------------

If constraint C (the FK) is deferred, it works as expected, because then the check happens at commit time, and the
referenced table's index is looked up again and we see the row containing 1. Even if the unique constraint is checked after the FK constraint at commit time, the presence of two "1" would not cause a problem; the constraint is satisfied when we find the (first) row containing 1 in the scan.


> A immediate Fk constraint blows up iff its referenced PK is deferred and we delete a duplicate
> ----------------------------------------------------------------------------------------------
>
>                 Key: DERBY-6559
>                 URL: https://issues.apache.org/jira/browse/DERBY-6559
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>            Reporter: Dag H. Wanvik
>            Assignee: Dag H. Wanvik
>
> Cf the following test case:
> {code:title=testFKPlusUnique|borderStyle=solid}
>     /**
>      * 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 broken.
>             
>             // 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. Even though we have
>             // default restrict action, there is another row that would
>             // satisfy the constraint.
>             rs.deleteRow();
>             
>             // Now there should be only one left, so the referenced table is
>             // OK.
>             commit();
>             :
> {code}
> 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.



--
This message was sent by Atlassian JIRA
(v6.2#6252)