[
https://issues.apache.org/jira/browse/DERBY-6461?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13901049#comment-13901049
]
Mamta A. Satoor commented on DERBY-6461:
----------------------------------------
I tried the above script in 10.9 branch but it failed because system procedure
syscs_register_tool is not available prior to 10.10 release (DERBY-6022). I
haven't had time to change the script to see if
DatabaseMetaData.getExportedKeys() and getImportedKeys() behavior reproduces on
10.9 and prior releases.
> 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)
