CODEWITHSUNDEEP
javajdbc
Fabio
Fabio

Reputation: 363

How to check if an index already exists in a table?

I have this method to check if a index already exists in a table. The query returns a table like this enter image description here

How can I check if the the table has already a row in the column Key_name?

 private boolean checkIndexExists(JdbcConnection connection,String indexName,String tableName) throws DatabaseException, SQLException {
        boolean exists=false;

        String searchStatement = "SHOW INDEXES FROM "+tableName;
        ResultSet columnsRs;
        Statement s = connection.createStatement();
        columnsRs = s.executeQuery(searchStatement);
        
        //CHECK IF THE INDEX ALREADY EXISTS

        return exists;
    }

Upvotes: 1

Views: 720

Answers (2)

miseleigh
miseleigh

Reputation: 1

Don't use SHOW. That's meant to display simple results, and isn't great for any kind of decisions or calculations.

Use COUNT:

private boolean checkIndexExists(Connection connection,String indexName,String tableName) throws SQLException {
    String stmt = "SELECT COUNT(Key_name) FROM "+tableName+" WHERE Key_name='"+indexName+"'";
    try(ResultSet rs = connection.createStatement().executeQuery(stmt);){
        return rs.getInt("COUNT") > 0;
    }
}

SQL COUNT returns NULL if there are no rows to count from, but getInt interprets NULL as 0, so we don't need to check for NULL this way. Also, note that a ResultSet is a resource and should be closed, either in a try-catch-finally or (preferred) try-with-resources as shown here.

And depending on what you're doing, it could be more useful to just return the index:

private int getCurrentIndex(Connection connection,String indexName,String tableName) throws SQLException {
    String stmt = "SELECT MAX(Seq_in_index) FROM "+tableName+" WHERE Key_name='"+indexName+"'";
    try(ResultSet rs = connection.createStatement().executeQuery(stmt);){
        return rs.getInt("MAX");
    }
}

There is also the EXISTS keyword in SQL, but it's usually used within a larger statement, and would be more complicated for this use case. However, if you're trying to insert a new row and set the Seq_in_index accordingly, you could use EXISTS to do it all in one transaction.

Upvotes: 0

mihajlo
mihajlo

Reputation: 71

One way would be to go through the result set. You could go through all the rows of the table and and check if the value of Key_name is equal to indexName.

private boolean checkIndexExists(
    final JdbcConnection connection,
    final String indexName,
    final String tableName
) throws DatabaseException, SQLException {
    final String searchStatement = "SHOW INDEXES FROM " + tableName;
    final Statement statement = connection.createStatement();
    final ResultSet resultSet = statement.executeQuery(searchStatement);

    while (resultSet.next()) {
        if (indexName.equals(resultSet.getString("Key_name"))) {
            return true;
        }
    }
    return false;
}

Upvotes: 1

Related Questions