How to count the number of foreign keys in a SQL database using java

Question

How to count the number of foreign keys in a SQL database using java. I have been given a database and there are many tables inside that. I have to count the total number of foreign keys present inside id. I am executing the queries via Java.

Query information_schema.STATISTICS table.. i believe the column `INDEX_TYPE` will tell you if the column is a foreign key or not. — Raymond Nijland, Mar 23 '18 at 13:55
https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html. If you are using JDBC. — Black_Buster, Mar 23 '18 at 13:58

score 0 · Accepted Answer · answered Mar 23 '18 at 14:00

All you have to do is execute following query using Java on your database.

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>'

Reference: How do I see all foreign keys to a table or column?

score 0 · Answer 2 · answered Mar 23 '18 at 14:21

Vendor independently one can use the DatabaseMetaData with its getExportedKeys and getImportedKeys on all tables.

DatabaseMetaData meta = conn.getMetaData();
try (ResultSet rs = meta.getExportedKeys(conn.getCatalog(), null, null)) {
    while (rs.next()) {
        String pkTable = rs.getString("PKTABLE_NAME");
        String fkTable = rs.getString("FKTABLE_NAME");
        String fkColumn = rs.getString("FKCOLUMN_NAME");
        int keySeq = rs.getInt("KEY_SEQ");
        ...
    }
}

How to count the number of foreign keys in a SQL database using java

2 Answers2