[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=13989624#comment-13989624 ] 

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

Thinking about this, I am not sure what would be the best behaviour here. I *think* it would behoove the referenced constraint (which is deferred unique) to change the way it checks and dependents (referencing foreign keys) so that
are not violated if there is a least one row in the "unique-to-be" index that satisfies the FK reference. This will lead us to go back to the "unique-to-be" table and only propagate checks on delete/modify of dependents is the *last* row with the relevant unique key is deleted/modified. This might lead to different performance/locking behavior, but only for the new, deferrable code paths.

Another option is to continue to throw here, and just require users to make the FK deferred and NO ACTION if they want the FKs to be unaffected of the referenced tables "deferredness".

Opinions?


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