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.

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks