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 "Mamta A. Satoor (JIRA)" <ji...@apache.org> on 2014/02/12 18:24:19 UTC
[jira] [Commented] (DERBY-6461) DatabaseMetaData.getExportedKeys()
and getImportedKeys() incorrectly return information on UNIQUE constraints
[ https://issues.apache.org/jira/browse/DERBY-6461?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13899298#comment-13899298 ]
Mamta A. Satoor commented on DERBY-6461:
----------------------------------------
Same behavior on 10.10 too.
> DatabaseMetaData.getExportedKeys() and getImportedKeys() incorrectly return information on UNIQUE constraints
> -------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-6461
> URL: https://issues.apache.org/jira/browse/DERBY-6461
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.11.0.0
> Reporter: Rick Hillegas
>
> DatabaseMetaData.getExportedKeys() and getImportedKeys() are only supposed to return information on foreign keys which reference primary keys. But Derby also returns information on foreign keys which reference unique keys also. This can give rise to ambiguous results. The following script shows this behavior:
> connect 'jdbc:derby:memory:db;create=true';
> create table t1
> (
> primaryColumn int not null,
> uniqueColumn int not null,
> constraint t1_primary primary key( primaryColumn ),
> constraint t1_unique unique( uniqueColumn )
> );
> create table t2
> (
> key1 int not null,
> key2 int not null,
> constraint t2_foreign1 foreign key( key1 ) references t1( primaryColumn ),
> constraint t2_foreign2 foreign key( key2 ) references t1( uniqueColumn )
> );
> call syscs_util.syscs_register_tool( 'databaseMetaData', true );
> -- works correctly. just returns the primary key
> select pk_name, column_name, key_seq
> from table( getPrimaryKeys( null, 'APP', 'T1' ) ) s
> order by pk_name, key_seq;
> -- incorrect. returns info on foreign keys which reference unique keys. this creates ambiguous results.
> select fktable_name, fkcolumn_name, pktable_name, pkcolumn_name, key_seq
> from table( getExportedKeys( null, 'APP', 'T1' ) ) s
> order by fktable_name, pktable_name, key_seq;
> -- incorrect. returns info on foreign keys which reference unique keys. this creates ambiguous results.
> select fktable_name, fkcolumn_name, pktable_name, pkcolumn_name, key_seq
> from table( getImportedKeys( null, 'APP', 'T2' ) ) s
> order by fktable_name, pktable_name, key_seq;
--
This message was sent by Atlassian JIRA
(v6.1.5#6160)