getIndexInfo为复合主键返回错误的唯一列 [英] getIndexInfo returning wrong unique columns for composite primary keys

问题描述

我正在使用JDBC，并且已经使用postgresql创建了一个简单的表:

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)
);

问题是当我检查bank_id是否唯一时，我得到了true(当我期望false时，ofc).这是我的函数的代码，该代码检查表中的列是否唯一:

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.

推荐答案

getIndexInfo()为每一列和索引返回一行.而且这两列都是主键的一部分，因此您在ResultSet中获得两行，每一列各占一行.

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.

如果要检查列是否唯一"是唯一的，则还需要计算每个索引返回的行数getIndexInfo().

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.

在您的情况下，ResultSet看起来像这样:

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!

如果在 only bank_id上存在唯一索引，您将具有以下内容:

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为复合主键返回错误的唯一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！