Getting unique constraint column names from oracle database

Question

I am implementing a search functionality from UI for this I want to give a drop down of column names which have unique constraints along with any primary key column if present, so user can search with any of these selected column relate data. I have searched for such query but haven't found

Something like:

SELECT COLUMN_NAMEs FROM TABLE WHERE CONSTRAINTS UNIQUE OR PRIMARY

Is there any query to achieve this ...

Answer 1

USER_CONSTRAINTS would return foreign keys also. You need only primary and unique keys. But uniqueness can be achieved via unique index too. It won't be shown in constraints list. You need to watch USER_INDEXES view. The good point is that primary and unique keys create corresponding unique indexes. So, it's necessary and sufficient to check USER_INDEXES.

UPD: see Lalit Kumar B's comment.

select c.COLUMN_NAME
from USER_INDEXES i, USER_IND_COLUMNS c
where i.TABLE_NAME = 'YOUR_TABLE'
  and i.UNIQUENESS = 'UNIQUE'
  and i.TABLE_NAME = c.TABLE_NAME
  and i.INDEX_NAME = c.INDEX_NAME
union
select cc.COLUMN_NAME
from USER_CONSTRAINTS con, USER_CONS_COLUMNS cc
where con.TABLE_NAME = 'YOUR_TABLE'
  and con.CONSTRAINT_TYPE in ( 'U', 'P' )
  and con.TABLE_NAME = cc.TABLE_NAME
  and con.CONSTRAINT_NAME = cc.CONSTRAINT_NAME