从Oracle数据库获取唯一约束列名称 [英] Getting unique constraint column names from oracle database

问题描述

我正在为此从UI中实现搜索功能，我想给出一个具有唯一约束的列名称以及任何主键列(如果存在)的下拉列表，以便用户可以使用任何这些选定的列相关数据进行搜索.我已经搜索了此类查询，但没有找到

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

类似:

SELECT COLUMN_NAMEs FROM TABLE WHERE CONSTRAINTS UNIQUE OR PRIMARY

是否有任何查询来实现这一目标...

Is there any query to achieve this ...

推荐答案

USER_CONSTRAINTS也将返回外键.您只需要主键和唯一键.但是也可以通过唯一索引来实现唯一性.它不会显示在约束列表中.您需要观看USER_INDEXES视图.优点是主键和唯一键创建相应的唯一索引.因此，有必要检查USER_INDEXES.

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:请参见 Lalit Kumar B 的评论.

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

这篇关于从Oracle数据库获取唯一约束列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！