Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register


1
(click on this box to dismiss)

How to navigate foreign key relationships using the dictionary views?

I am implementing a CUBRID integration for jOOQ, and I wonder how I can discover foreign key relationships using the dictionary views. Using the following query, I can discover foreign key columns:

select k.class_name, k.index_name, k.key_attr_name
from db_index  i
join db_index_key k
on i.index_name = k.index_name
and i.class_name = k.class_name
where is_foreign_key = 'YES'
order by k.class_name, k.index_name, k.key_order

Now, how can I discover which columns (or which primary key) is referenced?

link comment (0) accepted answer
asked 2 years ago
lukaseder
63
11 Answers
1

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=?";

link comment (2)
answered 2 years ago
dba
558
0

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

link comment (1)
answered 2 years ago
hellohuizhang
51




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: