This discussion is archived
0 Replies Latest reply: Jul 29, 2013 2:25 AM by AlexandreGarnier RSS

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

AlexandreGarnier Newbie
Currently Being Moderated

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 ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points