Getting Foreign Keys using DatabaseMetaData not working

[Nitin Singh]

I have been trying to use DataBaseMetaData methods getImportedKeys, getExportedKeys or getCrossReference for getting foreign keys of a table but all it returns is empty resultset. I also used getPrimaryKeys method with ucanaccess and it worked perfectly.

Has anyone else been facing same issue or any alternatives for getting foreign keys for the table.

[Gord Thompson]

I am unable to recreate your issue using UCanAccess 3.0.4. This code works fine for me

ResultSet rs = conn.getMetaData().getImportedKeys(
        null, null, "Donations");
while (rs.next()) {
    System.out.printf(
            "[%s].[%s] --> [%s].[%s]%n", 
            rs.getString("FKTABLE_NAME"), 
            rs.getString("FKCOLUMN_NAME"), 
            rs.getString("PKTABLE_NAME"), 
            rs.getString("PKCOLUMN_NAME"));
}

[Nitin Singh]

I am using UCanAccess 3.0.4 with Java 1.8.0_71.
This is my code. It is not working for the attached Access File

public class testMsAccess {

    public static void main(String[] args) {
        List<String> pk_tab = new ArrayList<String>();
        List<String> pk_col = new ArrayList<String>();
        List<String> fk_tab = new ArrayList<String>();
        List<String> fk_col = new ArrayList<String>(); 
        List<String> primary = new ArrayList<String>();
        try {
            Connection conn = getConnection();
            DatabaseMetaData dbm = conn.getMetaData();
            ResultSet keys = dbm.getImportedKeys(null, null, "Book");
            while(keys.next()) {
                pk_tab.add(keys.getString("PKTABLE_NAME"));
                pk_col.add(keys.getString("PKCOLUMN_NAME"));
                fk_tab.add(keys.getString("FKTABLE_NAME"));
                fk_col.add(keys.getString("FKCOLUMN_NAME"));
            }
            keys.close();
            conn.close();
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * Get Connection to Access database
     */
    public static Connection getConnection() throws Exception {
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            String path = "C:/Users/nisingh/Documents/BugFiles/B34725 - MsAccess import issue/Database1.accdb";
            return DriverManager.getConnection("jdbc:ucanaccess://"+path);
        }
        catch (SQLException ex) {
            throw new Exception("Cannot read MS Access Database file");
        }
    }
}

[Gord Thompson]

The "Relationships" defined in your Access database do not have "Enforce Referential Integrity" enabled, so they are actually not foreign key constraints. If you enable "Enforce Referential Integrity" for the Relationships in Access then getImportedKeys and getExportedKeys in JDBC will work as expected.

[Nitin Singh]

Thank you so much helping out through this, I checked with "Enforce Referential Integrity" enabled and now i am able to correctly retrieve the foreign keys. I'm still in a fix because this is not the usual practice for many of our customers, who do not have this option checked. We may have to enforce this constraint over them.

Can you still think of an alternative to this?
And my sincere thanks once again.

[Gord Thompson]

You could use the Jackcess API to retrieve the Relationships information directly. The following code ...

try (Database db = DatabaseBuilder.open(new File("/home/gord/Downloads/Database1.accdb"))) {
    for (Relationship r : db.getRelationships()) {
        System.out.printf("%nRelationship Name: %s%n", r.getName());
        Object[] toColumns = r.getToColumns().toArray();
        Object[] fromColumns = r.getFromColumns().toArray();
        for (int i = 0; i < fromColumns.length; i++) {
            System.out.printf(
                    "[%s].[%s] --> [%s].[%s]%n", 
                    r.getToTable().getName(), 
                    ((Column)toColumns[i]).getName(), 
                    r.getFromTable().getName(), 
                    ((Column)fromColumns[i]).getName());
        }
    }
} catch (Exception e) {
    e.printStackTrace(System.err);
}

 
... produces ...

Relationship Name: AuthorBook
[Book].[Book_Author] --> [Author].[Author]

Relationship Name: BookChart
[Chart].[Book] --> [Book].[Book]

Relationship Name: AuthorChart
[Chart].[Authored By] --> [Author].[Author]

[Nitin Singh]

Thanks, i checked with jackcess and it works perfectly as mentioned by you. This really is a big help.