You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Dag H. Wanvik (JIRA)" <ji...@apache.org> on 2014/04/30 00:31:19 UTC
[jira] [Created] (DERBY-6559) A immediate Fk constraint blows up
iff its referenced PK is deferred and we delete a duplicate
Dag H. Wanvik created DERBY-6559:
------------------------------------
Summary: 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:
/**
* 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.
--
This message was sent by Atlassian JIRA
(v6.2#6252)