[DDLUTILS-219] find oracle foreign keys with the user_constraints view, not from jdbc metadata

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.0
• Component/s: Core - Oracle
• Assignee: Thomas Dudziak
• Reporter: Chris Hyzer
• Created: 29/Jul/08 16:14
• Updated: 29/Jul/08 16:14

Description

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