愿所有的美好和期待都能如约而至

在 Oracle SQL 中显示表的所有约束的名称

发布时间:  来源:互联网  作者:匿名  标签:constraints error Display names of all constraints for a table in Oracle SQL exc  热度:37.5℃

本文介绍了在 Oracle SQL 中显示表的所有约束的名称的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我为在 Oracle SQL 中创建的多个表的每个约束定义了一个名称.

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL.

问题是要删除特定表的列的约束,我需要知道我为每个约束提供的名称,但我已经忘记了.

The problem is that to drop a constraint for the column of a particular table I need to know the name that I have supplied for each constraints, which I have forgotten.

如何列出我为表的每一列指定的所有约束名称?

How do I list out all the names of constraints that I have specified for each column of a table?

是否有任何 SQL 语句可以执行此操作?

Is there any SQL statement for doing so?

推荐答案

需要查询数据字典,特别是USER_CONS_COLUMNS视图查看表列和对应的约束:

You need to query the data dictionary, specifically the USER_CONS_COLUMNS view to see the table columns and corresponding constraints:

SELECT *
  FROM user_cons_columns
 WHERE table_name = '<your table name>';

仅供参考,除非您专门使用小写名称(使用双引号)创建了表格,否则表格名称将默认为大写,因此请确保在您的查询中也是如此.

FYI, unless you specifically created your table with a lower case name (using double quotes) then the table name will be defaulted to upper case so ensure it is so in your query.

如果您希望查看有关约束本身的更多信息,请查询 USER_CONSTRAINTS 视图:

If you then wish to see more information about the constraint itself query the USER_CONSTRAINTS view:

SELECT *
  FROM user_constraints
 WHERE table_name = '<your table name>'
   AND constraint_name = '<your constraint name>';

如果表保存在不是默认架构的架构中,那么您可能需要将视图替换为:

If the table is held in a schema that is not your default schema then you might need to replace the views with:

all_cons_columns

all_constraints

添加到 where 子句:

adding to the where clause:

   AND owner = '<schema owner of the table>'

这篇关于在 Oracle SQL 中显示表的所有约束的名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,

勇敢去编程!

勇敢的热爱编程,未来的你一定会大放异彩,未来的生活一定会因编程更好!

TOP