How Can You Determine Where a Given Table Is Referenced via Foreign Keys? JDBC

DatabaseMetaData.getExportedKeys() returns a ResultSet object, which relates to other tables that reference the given table as a foreign key container. In other words, it tells us which tables have foreign keys that reference this table. A primary key (PK) is a column or set of columns that uniquely identifies a row or record in a table. A foreign key (FK) is one or more columns in one table that are used as a primary key in another table. First, we’ll look at these concepts in a simple example, and then we’ll develop a JDBC solution and a test client program to show these relationships using DatabaseMetaData.getExportedKeys().

Oracle Database Setup

First, let’s create two tables (dept_table and emp_table) and define the PK and FK for these tables. Figure illustrates the relationship of these tables.

Relationship of tables:

Relationship of tables

Keep in mind that if you violate the PK and FK rules, the SQL INSERT operation will fail:

ERROR at line 1: ORA-02291: integrity constraint (SCOTT.SYS_C005465) violated - parent key not Found

Note Since dept 33 is not defined in dept_table, Oracle issues an error.

DatabaseMetaData.getExportedKeys() According to J2SE

public ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException

This method retrieves a description of the foreign key columns that reference the given table’s primary key columns (the foreign keys exported by a table). They are ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ. Each foreign key column description.

ResultSet Object’s Columns for Invoking getExportedKeys()

ResultSet Object’s Columns for Invoking getExportedKeys()

ResultSet Object’s Columns for Invoking getExportedKeys()

The method’s parameters are as follows:

  • catalog: A catalog name; it must match the catalog name as it is stored in this database."" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search.
  • schema: A schema name; it must match the schema name as it is stored in the database. "" retrieves those without a schema; null means that the schema name should not be used to narrow the search.
  • table: A table name; it must match the table name as it is stored in this database. This method returns a ResultSet object in which each row is a foreign key column description. If a database access error occurs, it throws a SQLException.

The Solution:Using DatabaseMetaData.getExportedKeys()

In using the DatabaseMetaData.getExportedKeys() method, try to pass all required parameters with non-null and non-empty values. Passing null/empty values might slow down getting the results from this method. If your database is not changing often, you may cache the returned values on the server side.

The Oracle Client Test Program

Running the Client Test Program

The MySQL Database Setup

In the current version of MySQL (version 4.0.8), only InnoDB table types support the foreign key concept. According to MySQL, starting with MySQL 5.1, foreign keys will be supported for all table types, not just InnoDB. Let’s create two tables (dept_table and emp_table) and define the PK and FK. Keep in mind that if you violate the PK and FK rules, the SQL INSERT operation will fail.

$ mysql --user=root --password=root Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 130 to server version: 4.0.18-nt

The MySQL Client Test Program

Running the Client Test Program


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics