db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chris Hyzer (JIRA)" <j...@apache.org>
Subject [jira] Created: (DDLUTILS-219) find oracle foreign keys with the user_constraints view, not from jdbc metadata
Date Tue, 29 Jul 2008 16:16:33 GMT
find oracle foreign keys with the user_constraints view, not from jdbc metadata
-------------------------------------------------------------------------------

                 Key: DDLUTILS-219
                 URL: https://issues.apache.org/jira/browse/DDLUTILS-219
             Project: DdlUtils
          Issue Type: Bug
          Components: Core - Oracle
    Affects Versions: 1.0
            Reporter: Chris Hyzer
            Assignee: Thomas Dudziak


If someone wants to help me put the code somewhere, I can code the fix for this...

Im pretty frustrated with this problem.  It works fine in mysql with ddlutils, but not wtih
oracle.  Then when I look at the jdbc api:
 
ResultSet java.sql.DatabaseMetaData.getImportedKeys(String catalog, String schema, String
table) throws SQLException
Retrieves a description of the *primary key columns* that are referenced by a table's foreign
key columns (the primary keys imported by a table). 
 
This says it only returns foreign keys that use another table's primary key, not foreign keys
which use another tables unique constraint.
 
So the end result is, I call table.getForeignKeys() in ddlutils, and it only returns the foreign
keys which are primary keys in another table.  Not all foreign keys get removed, and one tries
to get added which is already added, and it fails.
 
When I go straight to JDBC I also see the same problem:
 
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection connection = DriverManager.getConnection(URL, "authzadm", PASS);
    DatabaseMetaData databaseMetaData = connection.getMetaData();
    ResultSet fkData = null;
    try {
      fkData = databaseMetaData.getImportedKeys(null, "AUTHZADM", "GROUPER_ATTRIBUTES");
      ResultSetMetaData resultSetMetaData = fkData.getMetaData();
      int columnCount = resultSetMetaData.getColumnCount();
      int fk = 0;
      while (fkData.next()) {
        System.out.println(fk++ + ": ");
        for (int i = 1; i <= columnCount; i++) {
          System.out.println("  " + resultSetMetaData.getColumnName(i) + ": "
              + fkData.getString(i));
        }
      }
    } finally {
      if (fkData != null) {
        fkData.close();
      }
      connection.close();
    }
  }

 
PRINTS only 1 FK:
 
0: 

PKTABLE_CAT: null

PKTABLE_SCHEM: AUTHZADM

PKTABLE_NAME: GROUPER_GROUPS

PKCOLUMN_NAME: ID

FKTABLE_CAT: null

FKTABLE_SCHEM: AUTHZADM

FKTABLE_NAME: GROUPER_ATTRIBUTES

FKCOLUMN_NAME: GROUP_ID

KEY_SEQ: 1

UPDATE_RULE: null

DELETE_RULE: 1

FK_NAME: FK_ATTRIBUTES_GROUP_ID

PK_NAME: SYS_C0030322

DEFERRABILITY: 7

 

However, in the DB, there are two foreign keys:

 

R Table R Columns FK Name Table R Constraint R Type Columns
GROUPER_GROUPS ID FK_ATTRIBUTES_GROUP_ID GROUPER_ATTRIBUTES SYS_C0030322 P GROUP_ID
GROUPER_FIELDS NAME FK_ATTRIBUTES_FIELD_NAME GROUPER_ATTRIBUTES FIELDS_NAME_UNQ U FIELD_NAME


You can see one of them is type "U" which is on a unique constraint, this is the one not printed
by jdbc.

 

It seems like oracle is following the jdbc spec.  Any ideas for how to solve this???  I have
the latest oracle driver, and I am using the Oracle10 ddlutils platform...  shouldnt ddlutils
use oracl data dictionary for this instead???  (so it will work)

 

select * from user_constraints where table_name = 'GROUPER_ATTRIBUTES'

and CONSTRAINT_TYPE = 'R';

 

AUTHZADM FK_ATTRIBUTES_GROUP_ID R

GROUPER_ATTRIBUTES 


AUTHZADM SYS_C0030322 NO ACTION ENABLED 

NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 29-JUL-08




 

AUTHZADM FK_ATTRIBUTES_FIELD_NAME R

OWNER CONSTRAINT_NAME C

GROUPER_ATTRIBUTES 


AUTHZADM FIELDS_NAME_UNQ NO ACTION ENABLED 

NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 29-JUL-08




 

2 rows selected.

Thanks!

Chris




-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message