[jira] [Updated] (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:all-tabpanel ]

Dag H. Wanvik updated DERBY-6559:
---------------------------------

    Description: 
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();

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.
{code}

  was:
Cf the following test case:

    /**
     * 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();

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.



> 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();
> 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.
> {code}



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