[Java] Find a Database table's unique constraint

Question

Hi,

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

Hi Nils,

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

If Oracle creates indexes for unique constraints (I don't know if it does, you need to check) than you could find out about your constraints via getIndexInfo()

Answer 3

Unique constraints are usually enforced by a index. Perhaps use DatabaseMetaData.getIndexInfo() to find the indexes where the non-unique is false?