[jira] [Commented] (DERBY-6665) Violation of deferred foreign key not detected on commit

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

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

Knut Anders Hatlen commented on DERBY-6665:
-------------------------------------------

The two foreign keys FK1 and FK2 share the same physical conglomerate, since they are declared on the same column. When a violation of a deferred constraint is detected, it is registered in a hash table whose key is the physical conglomerate number. This means the deferred validation at commit-time cannot reliably tell whether it should check for FK1 or FK2 violations. In the example above, since the first constraint violation that is recorded is a violation of FK2 (for the "insert into t3 values 1" statement), the check on commit thinks all of the registered violations on that conglomerate were FK2 violations, and it only checks FK2. The violation of FK1 is therefore not detected.

I think the hash table (GenericLanguageConnectionContext.deferredHashTables) needs to be changed so that its key is an identifier of the logical conglomerate instead of the physical conglomerate, so that violations of FK1 and FK2 go into separate buckets and can be distinguished when the deferred check is performed. The conglomerate id (a UUID) can probably be used. It seems to be different for different logical conglomerates that share the same physical conglomerate.

> Violation of deferred foreign key not detected on commit
> --------------------------------------------------------
>
>                 Key: DERBY-6665
>                 URL: https://issues.apache.org/jira/browse/DERBY-6665
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.11.0.0
>            Reporter: Knut Anders Hatlen
>
> See the following script:
> {noformat}
> ij version 10.11
> ij> connect 'jdbc:derby:memory:db;create=true';
> ij> create table t1(x int primary key);
> 0 rows inserted/updated/deleted
> ij> create table t2(x int primary key);
> 0 rows inserted/updated/deleted
> ij> create table t3(x int, constraint fk1 foreign key (x) references t1 initially deferred, constraint fk2 foreign key (x) references t2 initially deferred);
> 0 rows inserted/updated/deleted
> ij> insert into t1 values 1;
> 1 row inserted/updated/deleted
> ij> autocommit off;
> ij> insert into t3 values 1;
> 1 row inserted/updated/deleted
> ij> insert into t2 values 1;
> 1 row inserted/updated/deleted
> ij> delete from t1;
> 1 row inserted/updated/deleted
> ij> commit;
> ij> select * from t1;
> X          
> -----------
> 0 rows selected
> ij> select * from t2;
> X          
> -----------
> 1          
> 1 row selected
> ij> select * from t3;
> X          
> -----------
> 1          
> 1 row selected
> {noformat}
> Since T3.X contains a value (1) that is not present in T1, the foreign key FK1 is violated, and the COMMIT statement should have failed.



--
This message was sent by Atlassian JIRA
(v6.2#6252)