public OracleFKHandler(WbConnection conn) {
super(conn);
currentUser = conn.getCurrentUser();
containsStatusCol = true;
// This is essentially a copy of the Statement used by the Oracle driver
// the driver does not take unique constraints into account, and this statement does.
// Otherwise foreign keys referencing unique constraints (rather than primary keys) would
// not be displayed (DbExplorer, WbSchemaReport) or correctly processed (TableDependency)
baseSql =
"-- SQLWorkbench \n"
+ "SELECT "
+ OracleUtils.getCacheHint()
+ " NULL AS pktable_cat, \n"
+ " p.owner AS pktable_schem, \n"
+ " p.table_name AS pktable_name, \n"
+ " pc.column_name AS pkcolumn_name, \n"
+ " NULL AS fktable_cat, \n"
+ " f.owner AS fktable_schem, \n"
+ " f.table_name AS fktable_name, \n"
+ " fc.column_name AS fkcolumn_name, \n"
+ " fc.position AS key_seq, \n"
+ " 3 AS update_rule, \n"
+ " decode (f.delete_rule, \n"
+ " 'CASCADE', 0, \n"
+ " 'SET NULL', 2, \n"
+ " 1 \n"
+ " ) AS delete_rule, \n"
+ " f.constraint_name AS fk_name, \n"
+ " p.constraint_name AS pk_name, \n"
+ " decode(f.deferrable, \n"
+ " 'DEFERRABLE', decode(f.deferred, 'IMMEDIATE', "
+ DatabaseMetaData.importedKeyInitiallyImmediate
+ ", "
+ DatabaseMetaData.importedKeyInitiallyDeferred
+ ") , \n"
+ " 'NOT DEFERRABLE',"
+ DatabaseMetaData.importedKeyNotDeferrable
+ " \n"
+ " ) deferrability, \n"
+ " case when f.status = 'ENABLED' then 'YES' else 'NO' end as enabled, \n"
+ " case when f.validated = 'VALIDATED' then 'YES' else 'NO' end as validated \n "
+ "FROM all_cons_columns pc, \n"
+ " all_constraints p, \n"
+ " all_cons_columns fc, \n"
+ " all_constraints f \n"
+ "WHERE f.constraint_type = 'R' \n"
+ "AND p.owner = f.r_owner \n"
+ "AND p.constraint_name = f.r_constraint_name \n"
+ "AND p.constraint_type IN ('P', 'U') \n"
+ // this is the difference to the original statement from the Oracle driver (it uses =
// 'P')
"AND pc.owner = p.owner \n"
+ "AND pc.constraint_name = p.constraint_name \n"
+ "AND pc.table_name = p.table_name \n"
+ "AND fc.owner = f.owner \n"
+ "AND fc.constraint_name = f.constraint_name \n"
+ "AND fc.table_name = f.table_name \n"
+ "AND fc.position = pc.position \n";
}
