SQL 从 oracle 数据库中获取唯一的约束列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28404046/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting unique constraint column names from oracle database
提问by rajasekhar
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
我正在为此从 UI 实现搜索功能，我想提供具有唯一约束的列名称的下拉列表以及任何主键列（如果存在），以便用户可以使用这些选定的列相关数据中的任何一个进行搜索。我已经搜索过这样的查询，但没有找到
Something like:
就像是：
SELECT COLUMN_NAMEs FROM TABLE WHERE CONSTRAINTS UNIQUE OR PRIMARY
Is there any query to achieve this ...
是否有任何查询可以实现此目的...
回答by Qualtagh
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.
USER_CONSTRAINTS 也会返回外键。您只需要主键和唯一键。但是唯一性也可以通过唯一索引来实现。它不会显示在约束列表中。您需要观看 USER_INDEXES 视图。好处是主键和唯一键创建了相应的唯一索引。因此，检查 USER_INDEXES 是必要且充分的。
UPD: see Lalit Kumar B's comment.
UPD：请参阅Lalit Kumar B的评论。
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
回答by Lalit Kumar B
You need to look at USER_CONS_COLUMNSview. And to get the constraint_type, you could join it with USER_CONSTRAINTS.
您需要查看USER_CONS_COLUMNS视图。要获得constraint_type，您可以将其与USER_CONSTRAINTS 一起使用。
SQL> desc user_cons_columns;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(128)
CONSTRAINT_NAME NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME VARCHAR2(4000)
POSITION NUMBER
SQL>
For example,
例如，
SQL> column owner format a6
SQL> column constraint_name format a10
SQL> column table_name format a10
SQL> column column_name format a10
SQL> SELECT A.owner,
2 A.constraint_name,
3 A.table_name,
4 A.column_name,
5 b.constraint_type
6 FROM user_cons_columns A,
7 user_constraints b
8 WHERE A.owner =b.owner
9 AND A.constraint_name=b.constraint_name
10 AND A.table_name =b.table_name;
OWNER CONSTRAINT TABLE_NAME COLUMN_NAM C
------ ---------- ---------- ---------- -
SCOTT FK_DEPTNO EMP DEPTNO R
SCOTT PK_DEPT DEPT DEPTNO P
SCOTT PK_EMP EMP EMPNO P
SQL>
