db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF subversion and git services (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6559) A immediate Fk constraint blows up iff its referenced PK is deferred and we delete a duplicate
Date Sat, 10 May 2014 21:55:55 GMT

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

ASF subversion and git services commented on DERBY-6559:
--------------------------------------------------------

Commit 1593557 from [~dagw] in branch 'code/trunk'
[ https://svn.apache.org/r1593557 ]

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

Patch derby-6559 changes ReferencedKeyRIChecker to omit checking
dependent tables iff the referenced key is deferred and has rows with
duplicate keys one of whom is attempted deleted. So, in effect, the
check in such a case happens only if the row is "the last of its
kind", i.e. the last row having a particular referenced key. Added
tests for this behavior. To determine whether we have duplicates, we
open an extra scan on the index.

> 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
>             Fix For: 10.11.0.0
>
>         Attachments: derby-6559.diff, derby-6559.status
>
>
> 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