What Foreign Keys Are Used in a Table? JDBC

DatabaseMetaData.getImportedKeys() returns a ResultSet object with data about foreign key columns, tables, sequence, and update and delete rules. DatabaseMetaData’s getImportedKeys() returns a ResultSet that retrieves a description of the primary key columns referenced by a table’s foreign key columns (the primary keys imported by a table). The ResultSet object’s records are ordered by the column names PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.

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.getImportedKeys().

Oracle Database Setup

Let’s create three tables (roles_table, emps_table, and emps_roles) and define the PK and FK.

relationships of these tables.

relationships of these tables.

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

SQL> select * from roles_table; SQL> select * from emps_table; SQL> select * from emps_roles;

DatabaseMetaData.getImportedKeys() Signature

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

This method 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). They are ordered by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ.

Each primary key column description has the columns shown in below Table

ResultSet Object’s Columns for Invoking getImportedKeys()

ResultSet Object’s Columns for Invoking getImportedKeys()

ResultSet Object’s Columns for Invoking getImportedKeys()

This method’s parameters are as follows:

  • catalog: A catalog name; it must match the catalog name as it is stored in the 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 the database.

This method returns a ResultSet in which each row is a primary key column description. If a database access error occurs, it throws a SQLException.

The Solution:Using DatabaseMetaData.getImportedKeys()

When using the DatabaseMetaData.getImportedKeys() 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. This method will give you a good idea about the dependency of your database tables.

Running the Client Test Program

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 1 to server version: 4.0.18-nt mysql> use octopus; Database changed mysql> create table emps_table ( -> badge_number varchar(5) not null, -> name varchar(20) not null, -> email varchar(20) not null, -> -> primary key (badge_number) -> ) TYPE=InnoDB; Query OK, 0 rows affected (0.24 sec) mysql> create table roles_table ( -> role varchar(5) not null, -> description varchar(25) not null, -> -> primary key (role) -> ) TYPE=InnoDB; Query OK, 0 rows affected (0.13 sec) mysql> create table emps_roles ( -> badge_number varchar(5) not null, -> role varchar(5) not null, -> -> primary key (badge_number, role), -> INDEX badge_number_index (badge_number), -> foreign key (badge_number) references emps_table(badge_number), -> INDEX role_index (role), -> foreign key (role) references roles_table(role) -> ) TYPE=InnoDB; Query OK, 0 rows affected (0.24 sec) mysql> select * from emps_table;

The MySQL Client Test Program

Running the Client Test Program



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

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

JDBC Topics