How to navigate foreign key relationships using the dictionary views?

Hi Lukas,

Currently there is no way to obtain cross-reference information using SQL. So far you can use the above mentioned query to get FK columns and index names in the source table. But I will file a feature request in our Issue Tracker.

Since you are using Java in your application, there is a workaround. Our CUBRID Manager database administration tool is implemented in Java. In CM there is exactly that functionality which you are looking for. If you double click on a table name which has a foreign key relationship with another table, the cross-reference information will be displayed.

You can refer to Verifying Foreign Key Information CUBRID Manual page for instructions. And below I am including the source code of CM which performs the functionality.

private Map<String, Map<String, String>> getForeignKeyInfo() throws SQLException {
	try {
		rs = connection.getMetaData().getImportedKeys("", "", tableName);
		Map<String, Map<String, String>> result = new HashMap<String, Map<String, String>>();
		while (rs.next()) 
		{
			Map<String, String> fkInfo = new HashMap<String, String>();
			fkInfo.put("PKTABLE_CAT", rs.getString("PKTABLE_CAT"));
			fkInfo.put("PKTABLE_SCHEM", rs.getString("PKTABLE_SCHEM"));
			fkInfo.put("PKTABLE_NAME", rs.getString("PKTABLE_NAME"));
			fkInfo.put("PKCOLUMN_NAME", rs.getString("PKCOLUMN_NAME"));
			fkInfo.put("FKTABLE_CAT", rs.getString("FKTABLE_CAT"));
			fkInfo.put("FKTABLE_SCHEM", rs.getString("FKTABLE_SCHEM"));
			fkInfo.put("FKTABLE_NAME", rs.getString("FKTABLE_NAME"));

			String fkColName = rs.getString("FKCOLUMN_NAME");
			fkInfo.put("FKCOLUMN_NAME", fkColName);
			fkInfo.put("KEY_SEQ", rs.getString("KEY_SEQ"));
			fkInfo.put("UPDATE_RULE", FOREIGN_KEY_ACTION_MAP.get(rs.getInt("UPDATE_RULE")));
			fkInfo.put("DELETE_RULE", FOREIGN_KEY_ACTION_MAP.get(rs.getInt("DELETE_RULE")));
			fkInfo.put("FK_NAME", rs.getString("FK_NAME"));
			fkInfo.put("PK_NAME", rs.getString("PK_NAME"));
			fkInfo.put("DEFERRABILITY", rs.getString("DEFERRABILITY"));
			result.put(fkColName, fkInfo);
		}
		return result;
	}
	finally {
		QueryUtil.freeQuery(rs);  
	}
 }

connection.getMetaData().getImportedKeys("", "", tableName);
getImportedKeysgetExportedKeys final String indexSql = "SELECT index_name, is_unique, is_reverse, is_primary_key, is_foreign_key,key_count FROM db_index where class_name=?";

Hello lukaseder!

PHP Driver has provided a function named cubrid_schema, and you can get foreign key relationships by it.

For example:

cubrid_schema($conn, CUBRID_SCH_IMPORTED_KEYS, $class_name): the result set contains all forergn keys in that tale and the primary keys to which they refer.

cubrid_schema($conn, CUBRID_SCH_EXPORTED_KEYS, $class_name): the result set contains the primary key of that table and all foreign keys that refer to it.

cubrid_schema($conn, CUBRID_SCH_CROSS_REFERENCE, $class_name, $attr_name): the result set contains the foreign key