Foreign Keys to create 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 for that object that are referenced by a table’s foreign key columns (the primary keys imported by a table). The ResultSet object’s records are ordered by 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/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, I show these concepts in a simple example, and then I develop a JDBC solution and a test client program to show these relationships using DatabaseMetaData. getImportedKeys().

Oracle Database Setup

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, then the SQL INSERT operation will fail.

Commit complete.

The signature of DatabaseMetaData.getImportedKeys() is

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 Table .

Table 9-3. ResultSet Columns for getImportedKeys()

ResultSet Columns for getImportedKeys()

ResultSet Columns for getImportedKeys()

The parameters are

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

MySQL Database Setup

In the current version of MySQL (version 4.0.18), 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.

ERROR 1216: Cannot add or update a child row: a foreign key constraint fails

The Solution

Invoking GetForeignKeys for MySQL

Figure : shows how to run the solution for the MySQL database.

Invoking GetForeignKeys for MySQLInvoking GetForeignKeys for MySQL

Invoking GetForeignKeys for Oracle

Figure : shows how to run the solution for the Oracle database.

Invoking GetForeignKeys for OracleInvoking GetForeignKeys for Oracle


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

JDBC Topics