[DERBY-6461] DatabaseMetaData.getExportedKeys() and getImportedKeys() incorrectly return information on UNIQUE constraints

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.10.1.1, 10.11.1.1
• Component/s: JDBC
• Labels: derby_backport_reject_10_10
• Urgency: Normal
• Issue & fix info: Repro attached
• Bug behavior facts: Deviation from standard
• Assignee: Unassigned
• Reporter: Richard N. Hillegas
• Created: 28/Jan/14 21:36
• Updated: 05/Mar/14 22:06

Description

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;