db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6665) Violation of deferred foreign key not detected on commit
Date Wed, 16 Jul 2014 11:52:04 GMT

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

Mime
View raw message