blocks|key|1139092|text|select+*+from+all_constraints
where+owner+=+'<NAME>'
and+constraint_name+=+'SYS_C00381400'
/|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1139093|与所有数据字典视图一样，如果您只想检查当前模式和管理用户的DBA_CONSTRAINTS视图，则这是一个USER_CONSTRAINTS视图。|unstyled|1139094|约束名称的结构指示系统生成的约束名称。例如，如果我们在表声明中指定NOT+NULL。或者实际上是主键或唯一键。例如：|1139095|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>|1139096|'C'用于检查，'P'用于主要。|offset|length|style|CODE|1139097|通常，给关系约束一个明确的名称是个好主意。例如，如果数据库为主键创建了一个索引(如果该列还没有索引，它就会这样做)，它将使用约束名称oo来命名索引。您不会想要一个充满了名为SYS_C00935191的索引的数据库。|1139098|老实说，大多数人都不屑于命名NOT+NULL约束。|1139099|entityMap^0|0|0|0|0|0|3|8|3|0|2E|D|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|X|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$B|C]]|$1|K|3|L|5|F|7|10|8|@$M|11|N|12|O|P]|$M|13|N|14|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|F|7|15|8|@$M|16|N|17|O|P]]|9|@]|A|$]]|$1|S|3|T|5|F|7|18|8|@]|9|@]|A|$]]|$1|U|3|-4|5|F|7|19|8|@]|9|@]|A|$]]]|V|$]]

<pre><code>select * from all_constraints
where owner = '&lt;NAME&gt;'
and constraint_name = 'SYS_C00381400'
/
</code></pre>

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.

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:

<pre><code>SQL&gt; create table t23 (id number not null primary key)
 2 /

Table created.

SQL&gt; 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&gt;
</code></pre>

<code>'C'</code> for check, <code>'P'</code> for primary. 

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 <code>SYS_C00935191</code>. 

To be honest most people don't bother naming NOT NULL constraints.

blocks|key|1139172|text|要获得更详细的描述(哪个表/列引用哪个表/列)，您可以运行以下查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1139173|SELECT+++uc.constraint_name%7C%7CCHR(10)
+++%7C%7C++++++'('%7C%7Cucc1.TABLE_NAME%7C%7C'.'%7C%7Cucc1.column_name%7C%7C')'+constraint_source
+++,+++++++'REFERENCES'%7C%7CCHR(10)
+++%7C%7C++++++'('%7C%7Cucc2.TABLE_NAME%7C%7C'.'%7C%7Cucc2.column_name%7C%7C')'+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;|code-block|syntax|javascript|1139174|来自here。|offset|length|1139175|entityMap|0|LINK|mutability|MUTABLE|url|http://blog.mclaughlinsoftware.com/2009/03/05/validating-foreign-keys/^0|0|0|2|4|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

To get a more detailed description (which table/column references which table/column) you can run the following query:

<pre><code>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;
</code></pre>

From <a href="http://blog.mclaughlinsoftware.com/2009/03/05/validating-foreign-keys/" rel="noreferrer">here</a>.

blocks|key|1354364|text|也许这能帮上忙..|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1354365|SELECT+constraint_name,+constraint_type,+column_name
from+user_constraints+natural+join+user_cons_columns
where+table_name+=+"my_table_name";|code-block|syntax|javascript|1354366|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

maybe this can help..

<pre><code>SELECT constraint_name, constraint_type, column_name
from user_constraints natural join user_cons_columns
where table_name = "my_table_name";
</code></pre>

I have a constraint called <code>users.SYS_C00381400</code>. How do I find what that constraint is? Is there a way to query all constraints?

Oracle find a constraint

数据库

我有一个名为users.SYS_C00381400的约束。我怎样才能找到这个约束是什么？有没有办法查询所有的约束？

问Oracle查找约束
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle查找约束EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle查找约束
EN