[
https://issues.apache.org/jira/browse/DERBY-6559?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13984925#comment-13984925
]
Dag H. Wanvik edited comment on DERBY-6559 at 5/5/14 3:37 PM:
--------------------------------------------------------------
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.
was (Author: dagw):
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 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)
