Verifying Foreign Key Information

Description

You can verify foreign key information by using getImportedKeys, getExportedKeys, and getCrossReference methods provided by DatabaseMetaData interface. Usage and examples of each method are as follows:

Syntax

getImportedKeys(String catalog, String schema, String table)

getExportedKeys(String catalog, String schema, String table)

getCrossReference(String parentCatalog, String parentSchema, String parentTable, String foreignCatalog, String foreignSchema, String foreignTable)

Return Value

When the methods above are called, the following ResultSet, consisting of 14 columns, is returned.

Name

Type

Note

PKTABLE_CAT

String

Always null

PKTABLE_SCHEM

String

Always null

PKTABLE_NAME

String

Table name of primary key

PKCOLUMN_NAME

String

Table name of primary key

FKTABLE_CAT

String

Always null

FKTABLE_SCHEM

String

Always null

FKTABLE_NAME

String

Table name of foreign key

FKCOLUMN_NAME

String

Column name of foreign key

KEY_SEQ

short

Sequence of foreign or primary keys (starting from 1)

UPDATE_RULE

short

A corresponding value to referring action defined as to foreign keys when primary keys are updated
Cascade=0, Restrict=2, No action=3, Set null=4

DELETE_RULE

short

A corresponding value to referring action defined as to foreign keys when primary keys are deleted
Cascade=0, Restrict=2, No action=3, Set null=4

FK_NAME

String

Foreign key name

PK_NAME

String

Primary key name

DEFERRABILITY

short

Always 6(DatabaseMetaData.importedKeyInitiallyImmediate)

Example

ResultSet rs = null;

 

                DatabaseMetaData dbmd = conn.getMetaData();

 

                System.out.println("\n===== Test getImportedKeys");

                System.out.println("=====");

                rs = dbmd.getImportedKeys(null, null, "pk_table");

                Test.printFkInfo(rs);

                rs.close();

 

                System.out.println("\n===== Test getExportedKeys");

                System.out.println("=====");

                rs = dbmd.getExportedKeys(null, null, "fk_table");

                Test.printFkInfo(rs);

                rs.close();

 

                System.out.println("\n===== Test getCrossReference");

                System.out.println("=====");

                rs = dbmd.getCrossReference(null, null, "pk_table", null, null, "fk_table");

                Test.printFkInfo(rs);

                rs.close();