哪个 Oracle 视图包含所有约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3786156/
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
Which Oracle view contains all constraints together?
提问by Ronaldus
I'm trying to get CONSTRAINTS from user_objects table like this:
我正在尝试从 user_objects 表中获取 CONSTRAINTS，如下所示：
select CASE object_type
WHEN 'DATABASE LINK' then 'dblinks'
WHEN 'FUNCTION' then 'functions'
WHEN 'INDEX' then 'indexes'
WHEN 'PACKAGE' then 'packages'
WHEN 'PROCEDURE' then 'procedures'
WHEN 'SEQUENCE' then 'sequences'
WHEN 'TABLE' then 'tables'
WHEN 'TRIGGER' then 'triggers'
WHEN 'VIEW' then 'views'
WHEN 'SYNONYM' then 'synonyms'
WHEN 'GRANT' then 'grants'
WHEN 'CONSTRAINT' then 'constraints'
ELSE object_type
END||'|'||
CASE object_type
WHEN 'DATABASE LINK' then 'DB_LINK'
ELSE object_type
END||'|'||object_name
from user_objects
where object_name not like 'BIN$%'
and object_type not like '%PARTITION'
and object_type not in ('PACKAGE BODY')
order by object_type
;
select distinct object_type
from user_objects
;
But..... USER_OBJECTS has only these types FUNCTION
INDEX, PACKAGE, PACKAGE BODY, PROCEDURE, SEQUENCE, TABLE, TRIGGER, VIEW because select distinct object_type from user_objects; returned them. So this query is not giving my the constraints at all.
但是..... USER_OBJECTS 只有这些类型 FUNCTION
INDEX, PACKAGE, PACKAGE BODY, PROCEDURE, SEQUENCE, TABLE, TRIGGER, VIEW 因为从 user_objects 中选择不同的 object_type；还给他们。所以这个查询根本没有给我的约束。
Is there a way to get all constraints from Oracle? Which Oracle view should I use?
有没有办法从 Oracle 获取所有约束？我应该使用哪个 Oracle 视图？
回答by Michael Pakhantsov
select * from user_constraints
回答by Codo
Constraints aren't objects. So they're in a different view, namely USER_CONSTRAINTS. For foreign constraints, you'll need a self join:
约束不是对象。所以他们有不同的看法，即USER_CONSTRAINTS。对于外部约束，您需要一个自连接：
select * from user_constraints c
left join user_constraints r on r.owner = c.r_owner and r.constraint_name = c.r_constraint_name
where c.constraint_type = 'R';
Some details can also be found in USER_CONS_COLUMNS.
还可以在 中找到一些详细信息USER_CONS_COLUMNS。
