[
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)
