[
https://issues.apache.org/jira/browse/DERBY-6559?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13990107#comment-13990107
]
Dag H. Wanvik commented on DERBY-6559:
--------------------------------------
Tried to read what other databases do, but hard to find it described.. I tried
with Oracle 11g and saw these two behaviors below.
{code:title=create-before-dups.sql|borderStyle=solid}
SQL> set autocommit off;
SQL> create table t(i number(5), j number(5), constraint c primary key(i)
deferrable);
Table created.
SQL> create table child(i number(5), constraint fk foreign key(i) references
t(i) on delete set null);
Table created.
SQL> insert into t values (1,2);
1 row created.
SQL> insert into child values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> set constraints c deferred;
Constraint set.
SQL> insert into t values (1,1);
1 row created.
SQL> select * from t;
I J
---------- ----------
1 2
1 1
SQL> select * from child;
I
----------
1
SQL> delete from t where j=2;
1 row deleted.
SQL> select * from child;
I
----------
1
SQL> rollback;
Rollback complete.
SQL> drop table child;
Table dropped.
SQL> drop table t;
Table dropped.
SQL> commit;
Commit complete.
SQL> spool off;
{code}
{code:title=create-while-dups.sql|borderStyle=solid}
SQL> set autocommit off;
SQL> create table t(i number(5), j number(5), constraint c primary key(i)
deferrable);
Table created.
SQL> set constraints c deferred;
Constraint set.
SQL> insert into t values (1,2);
1 row created.
SQL> insert into t values (1,1);
1 row created.
SQL> create table child(i number(5), constraint fk foreign key(i) references
t(i) on delete set null);
create table child(i number(5), constraint fk foreign key(i) references t(i) on
delete set null)
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SYSTEM.C) violated
SQL> insert into child values(1);
insert into child values(1)
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from t;
no rows selected
SQL> select * from child;
select * from child
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> delete from t where j=2;
0 rows deleted.
SQL> select * from child;
select * from child
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> rollback;
Rollback complete.
SQL> drop table child;
drop table child
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table t;
Table dropped.
SQL> commit;
Commit complete.
SQL> spool off;
{code}
> 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)
