getIndexInfo returning wrong unique columns for composite primary keys

Question

I am working with JDBC and I have created a simple table using postgresql:

create table bank (
    bank_id int,
    bank_name varchar(40),
    bank_address varchar(80),
    user_id int,
    user_first_name varchar(40),
    user_surname varchar(40),
    user_phone varchar(12),
    primary key(bank_id, user_id)
);

The problem is that when I check whether bank_id is unique, I get true (when I was expecting false, ofc). Here is the code for my function that checks whether a column in a table is unique:

private static boolean checkIfUnique(Connection conn, String tableName,
    String columnName) {

    try {
        DatabaseMetaData meta = conn.getMetaData();
        ResultSet rs = meta.getIndexInfo(null, null, tableName, true, true);

        while(rs.next()) {
            if (rs.getString(9).equals(columnName)) {
                return true;
            }
        }

        return false;

    } catch (Exception e) {

        System.err.println("Exception: " + e + "\n" + e.getMessage());
    }

    return false;
}

I'm not really sure what I did wrong. Any suggestion would help. Thank you.

Unrelated, but: you should make sure you are closing the ResultSet (in a `finally` block) once you are finished. — , Aug 28 '18 at 13:32
Thanks for the help. This works perfectly. Out of curiosity, what happens to ResultSet objects that are not closed? — Alexandru Cazan, Aug 30 '18 at 09:57

score 2 · Answer 1 · 2018-08-28T13:34:50.377

getIndexInfo() returns one row for each column and index. And both columns are part of the primary key, so you get two rows in the ResultSet, one for each column.

If you want to check if a column is unique "on its own", you also need to count how many rows getIndexInfo() returned for each index.

In your case the ResultSet would look something like this:

TABLE_NAME  | INDEX_NAME  | COLUMN_NAME
------------+-------------+------------
bank        | bank_pkey   | bank_id
bank        | bank_pkey   | user_id

Note there might be more unique indexes in that result!

If there was a unique index on only bank_id, you would have something like this:

TABLE_NAME  | INDEX_NAME  | COLUMN_NAME
------------+-------------+------------
bank        | idx_bank_id | bank_id

Only then, the column would be unique.

So in the loop you also need to count the number of columns per index in which the column to check participates. Only if the total number of columns in that index is one you can say that the column is unique.

getIndexInfo returning wrong unique columns for composite primary keys

1 Answers1

Linked