[
https://issues.apache.org/jira/browse/DERBY-6461?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Mamta A. Satoor updated DERBY-6461:
-----------------------------------
Affects Version/s: 10.10.1.1
> 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.10.1.1, 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)
