显示Oracle SQL中表的所有约束的名称 [英] Display names of all constraints for a table in 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，除非您特意使用小写名称（使用双引号）

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中表的所有约束的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！