SQL Oracle 查找约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2095415/
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
Oracle find a constraint
提问by David Oneill
I have a constraint called users.SYS_C00381400. How do I find what that constraint is? Is there a way to query all constraints?
我有一个名为users.SYS_C00381400. 我如何找到该约束是什么？有没有办法查询所有约束？
回答by APC
select * from all_constraints
where owner = '<NAME>'
and constraint_name = 'SYS_C00381400'
/
Like all data dictionary views, this a USER_CONSTRAINTS view if you just want to check your current schema and a DBA_CONSTRAINTS view for administration users.
与所有数据字典视图一样，如果您只想检查当前模式和 DBA_CONSTRAINTS 视图，则这是一个 USER_CONSTRAINTS 视图以供管理用户使用。
The construction of the constraint name indicates a system generated constraint name. For instance, if we specify NOT NULL in a table declaration. Or indeed a primary or unique key. For example:
约束名称的构造指示系统生成的约束名称。例如，如果我们在表声明中指定 NOT NULL。或者确实是主键或唯一键。例如：
SQL> create table t23 (id number not null primary key)
2 /
Table created.
SQL> select constraint_name, constraint_type
2 from user_constraints
3 where table_name = 'T23'
4 /
CONSTRAINT_NAME C
------------------------------ -
SYS_C00935190 C
SYS_C00935191 P
SQL>
'C'for check, 'P'for primary.
'C'用于检查，'P'用于初级。
Generally it's a good idea to give relational constraints an explicit name. For instance, if the database creates an index for the primary key (which it will do if that column is not already indexed) it will use the constraint name oo name the index. You don't want a database full of indexes named like SYS_C00935191.
一般来说，给关系约束一个明确的名字是个好主意。例如，如果数据库为主键创建索引（如果该列尚未编入索引，它将这样做）它将使用约束名称 oo 命名索引。您不希望数据库充满名为 like 的索引SYS_C00935191。
To be honest most people don't bother naming NOT NULL constraints.
老实说，大多数人不会费心命名 NOT NULL 约束。
回答by Thiago
To get a more detailed description (which table/column references which table/column) you can run the following query:
要获得更详细的描述（哪个表/列引用哪个表/列），您可以运行以下查询：
SELECT uc.constraint_name||CHR(10)
|| '('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' constraint_source
, 'REFERENCES'||CHR(10)
|| '('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' references_column
FROM user_constraints uc ,
user_cons_columns ucc1 ,
user_cons_columns ucc2
WHERE uc.constraint_name = ucc1.constraint_name
AND uc.r_constraint_name = ucc2.constraint_name
AND ucc1.POSITION = ucc2.POSITION -- Correction for multiple column primary keys.
AND uc.constraint_type = 'R'
AND uc.constraint_name = 'SYS_C00381400'
ORDER BY ucc1.TABLE_NAME ,
uc.constraint_name;
From here.
从这里开始。
回答by Van Gogh
maybe this can help..
也许这可以帮助..
SELECT constraint_name, constraint_type, column_name
from user_constraints natural join user_cons_columns
where table_name = "my_table_name";
