db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (DERBY-6559) A immediate Fk constraint blows up iff its referenced PK is deferred and we delete a duplicate
Date Mon, 05 May 2014 15:50:14 GMT

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

Dag H. Wanvik edited comment on DERBY-6559 at 5/5/14 3:48 PM:
--------------------------------------------------------------

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 any
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 if 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?



was (Author: dagw):
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 any
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)

Mime
View raw message