Skip to Main Content

Java and JavaScript in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

[BUG REPORT] JDBC Driver DatabaseMetaData.getCrossReference contract not respected

AlexandreGarnierJul 24 2013

Since JDK 6, DatabaseMetaData.getCrossReference contract is :

Retrieves a description of the foreign key columns in the given foreign key table that reference the primary key or the columns representing a unique constraint of the parent table (could be the same or a different table).

But the Oracle JDBC Driver executed query is :

SELECT

  NULL AS pktable_cat,

  p.owner as pktable_schem,

  p.table_name as pktable_name,

  pc.column_name as pkcolumn_name,

  NULL as fktable_cat,

  f.owner as fktable_schem,

  f.table_name as fktable_name,

  fc.column_name as fkcolumn_name,

  fc.position as key_seq,

  NULL as update_rule,

  decode (f.delete_rule, 'CASCADE', 0, 'SET NULL', 2, 1) as delete_rule,

  f.constraint_name as fk_name,

  p.constraint_name as pk_name,

  decode(f.deferrable, 'DEFERRABLE', 5, 'NOT DEFERRABLE', 7, 'DEFERRED', 6) deferrability

FROM

  all_cons_columns pc,

  all_constraints p,

  all_cons_columns fc,

  all_constraints f

WHERE 1 = 1

  AND p.table_name = :1

  AND f.table_name = :2

  AND p.owner = :3

  AND f.owner = :4

  AND f.constraint_type = 'R'

  AND p.owner = f.r_owner

  AND p.constraint_name = f.r_constraint_name

  AND p.constraint_type = 'P'

  AND pc.owner = p.owner

  AND pc.constraint_name = p.constraint_name

  AND pc.table_name = p.table_name

  AND fc.owner = f.owner

  AND fc.constraint_name = f.constraint_name

  AND fc.table_name = f.table_name

  AND fc.position = pc.position

ORDER BY fktable_schem, fktable_name, key_seq

;

The right query to get foreign keys that reference a unique constraint is :

[...]

  AND p.constraint_type in ('P', 'U')

[...]

Am I missing something ? Is there a workaround ?

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 21 2013
Added on Jul 24 2013
0 comments
1,352 views