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.
