Find a Database table's unique constraint

Question

I'm trying to find the unique constraints of a table using Java (on an Oracle Database, but that should make no difference).

I found a way to discover the Primary Keys of a table, thanks to DatabaseMetaData's getPrimaryKeys(....); However I was unable to find the unique constaints of the tables, and the Internet was not able to help me, therefore I'm ending here asking my question :)

Is there a clean way to find the unique constraints (or, rather, the name of the columns that must be unique for a table.. Well you get it hehe) of a table ? Best regards,

Nils

Answer 1

you can query the data dictionary:

SQL> SELECT cc.*
  2    FROM all_constraints c
  3    JOIN all_cons_columns cc ON (c.owner = cc.owner
  4                             AND c.constraint_name = cc.constraint_name)
  5   WHERE c.constraint_type = 'U'
  6     AND c.table_name = 'T';

OWNER      CONSTRAINT_NAME   TABLE_NAME     COLUMN_NAME     POSITION
---------- ----------------- -------------- ------------- ----------
VNZ        UNIQUE_COL        T              COLUMN1                1
VNZ        UNIQUE_COL        T              COLUMN2                2
VNZ        UNIQUE_COL2       T              COLUMN2                1

Answer 2

Since most databases store these constraints as an index, you can use DatabaseMetaData.getIndexInfo() as previously mentioned. This worked well for me when using Postgresql.

It's only important to call getIndexInfo() with the 4th parameter as true as the documenation says:

unique - when true, return only indices for unique values; when false, return indices regardless of whether unique or not

With the following code:

// Class to combine all columns for the same index into one object
public static class UniqueConstraint {
    public String table;
    public String name;
    public List<String> columns = new ArrayList<>();
    public String toString() {
        return String.format("[%s] %s: %s", table, name, columns);
    }
}

public static List<UniqueConstraint> getUniqueConstraints(Connection conn, String schema, String table) throws SQLException {
    Map<String, UniqueConstraint> constraints = new HashMap<>();

    DatabaseMetaData dm = conn.getMetaData();
    ResultSet rs = dm.getIndexInfo(null, schema, table, true, true);
    while(rs.next()) {
        String indexName = rs.getString("index_name");
        String columnName = rs.getString("column_name");

        UniqueConstraint constraint = new UniqueConstraint();
        constraint.table = table;
        constraint.name = indexName;
        constraint.columns.add(columnName);

        constraints.compute(indexName, (key, value) -> {
            if (value == null) { return constraint; }
            value.columns.add(columnName);
            return value;
        });
    }

    return new ArrayList<>(constraints.values());
}

you can call:

getUniqueConstraints(conn, "public", tableName);

and get back a list of all the unique constraints for a given table. The constraints are grouped by index since one index can cover multiple columns if they are only unique in combination.