Multi-column foreign key support?

Help
GrantM
2009-01-04
2013-05-29
  • GrantM
    GrantM
    2009-01-04

    I've got foreign keys in my Oracle database that are multi column.  It doesn't look like SchemaSpy support this as the constraint tab only shows a child column and a parent column. Also  my multi column constraints are not even being listed in that tab.   It would nice if it would at least show the constraints even if doesn't show the columns. 

     
    • John Currier
      John Currier
      2009-01-05

      Do the multi-column foreign key constraints show up somewhat correctly in the details of the child table?  If they do then they should also show up on the constraints tab.

      Note that the constraints tab assumes that all foreign key constraints will have a distinct name (ignoring case).  Is that not true here?

      John

       
    • GrantM
      GrantM
      2009-01-06

      As far as I can tell they don't show up anywhere in the tool.  They all have unique names and other than being multi-column they are no different from the other foreign keys.

       
      • John Currier
        John Currier
        2009-01-06

        I wonder if the Oracle that you're using somehow treats them differently than other foreign keys.  The only thing I can think of is that it's creating check constraints (or something similar) under the covers, but those would show up as well.

        As far as I know they're showing correctly for other people since it's never been reported.  I assume that there weren't any errors reported during analysis.

        John

         
    • GrantM
      GrantM
      2009-01-09

      I finally took to tracing the sql you were using to see what I was missing.  It turns out that it wasn't a problem with multi-column foreign keys but foreign keys that are enforced by a unique index.  This is a leqal way in Oracle to enforce a foreign key relationship with something other than a PK.  I believe to fix the issue you would need to change the query you use that queries
            FROM all_cons_columns pc, all_constraints p,
            all_cons_columns fc, all_constraints f

      and looks for the following
        AND p.constraint_type = 'P'

      which if changed to the following will pickup these foreign keys
        AND p.constraint_type in ('P','U')

      Grant

       
      • John Currier
        John Currier
        2009-01-09

        SchemaSpy is calling the Oracle JDBC driver's getImportedKeys() method to retrieve those details.  It's the one performing the query and determining that those aren't foreign keys.  It may be a legal way to enforce it, but the Oracle driver is saying they're not truly foreign keys.

        Is it possible that a different Oracle driver might perform the query as you expect?  Or that there's an option that you could give to the driver (via SchemaSpy's -connprops option) that will change the query?

        John

         
  • Tim Delaney
    Tim Delaney
    2012-07-03

    FWIW I modified ojdbc6.jar so that getImportedKeys() and getExportedKeys() treat unique columns as primary keys i.e.

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

    Now relationships in Oracle DBs like the above work just fine. Unfortunately, distributing the modified driver is probably of dubious legality …

    For someone who wants to try this themselves, I used ASM (specifically the Eclipse plugin) to generate an ASM-ified java file that could generate the bytecode for the OracleDatabaseMetaData class, modified the single case of

        AND p.constraint_type = 'P'

    to

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

    then modified the classfile so it would compile (i.e. removed the line import org.objectweb.asm.attrs.*;) and added a main() method to dump the output of dump() to OracleDatabaseMetaData.class which I then included in a renamed odbc6.jar. I've tested the resultant driver with both JDK6 and JDK7.

     
  • Tim Delaney
    Tim Delaney
    2012-07-03

    Bah - where's an edit option?

    I generated the ASM-ified java file from the OracleDatabaseMetaData.class in the ojdbc6.jar.

    And I modified the java file so it would compile. Obviously when compiling/running the classfile generator you need to have the ASM jar in your classpath.