SQL 查找列是否具有唯一约束

Find if a column has unique constraint

提问by dmvianna

In a hypothetical scenario, I am an user with no table creation privileges. I want to know if a column in a table has UNIQUE CONSTRAINT. Is it possible to look it up in the DICTIONARY? How would I go about it?

在一个假设的场景中，我是一个没有表创建权限的用户。我想知道表中的列是否具有UNIQUE CONSTRAINT。可以在DICTIONARY 中查找吗？我该怎么办？

采纳答案by SebastianH

Both answers given here miss one way to enforce uniqueness on a column: by creating a unique index(without defining a unique constraint on the column). See these two links (one, two) if you are not familiar with this option.

这里给出的两个答案都遗漏了一种强制列唯一性的方法：通过创建唯一索引（不对列定义唯一约束）。如果您不熟悉此选项，请参阅这两个链接（一、二）。

This check should be performed additionallyto the unique constraint check:

除了唯一约束检查之外，还应执行此检查：

select count(*) from
USER_IND_COLUMNS cols
where cols.table_name='YOUR_TABLE_NAME'
and cols.COLUMN_NAME='YOUR_COLUMN';

To check for a unique constraint use the already provided method:

要检查唯一约束，请使用已经提供的方法：

select count(*) cnt 
from user_constraints uc
where uc.table_name='YOUR_TABLE_NAME'
and uc.constraint_type='U';

Alternatively you can also look in the ALL_CONSTRAINTSand ALL_IND_COLUMNSviews.

或者，您也可以查看ALL_CONSTRAINTS和ALL_IND_COLUMNS视图。

回答by DazzaL

for unique constraints you can do something like:

对于唯一约束，您可以执行以下操作：

select cons.constraint_type, 
       all_cols.owner, all_cols.constraint_name, 
       all_cols.table_name, 
       all_cols.column_name, 
       all_cols.position
  from all_cons_columns col
       inner join all_cons_columns all_cols
               on col.owner = all_cols.owner
              and col.constraint_name = all_cols.constraint_name
       inner join all_constraints cons
               on col.owner = cons.owner
              and col.constraint_name = cons.constraint_name
 where col.owner = 'SCHEMA'
   and col.table_name = 'FOO'
   and col.column_name = 'ID'
   and cons.constraint_type in ('U', 'P')
 order by owner, constraint_name, position;

set the owner, table and column of interest and it will show you all constraints that cover that column

设置所有者、表和感兴趣的列，它将显示涵盖该列的所有约束

Note that this won't show all cases where a unique index exists on a column (as its possible to have a unique index in place without a constraint being present).

请注意，这不会显示列上存在唯一索引的所有情况（因为它可能在不存在约束的情况下拥有唯一索引）。

example:

例子：

SQL> create table foo(id number, id2 number, constraint foo_con unique(id, id2), constraint foo_con2 unique(id));

Table created.

now list all constraints that cover id:

现在列出涵盖的所有约束id：

SQL> col column_name format a20
SQL> col constraint_name format a20
SQL> col table_name format a15
SQL> select cons.constraint_type,
  2         all_cols.owner, all_cols.constraint_name,
  3         all_cols.table_name,
  4         all_cols.column_name,
  5         all_cols.position
  6    from all_cons_columns col
  7         inner join all_cons_columns all_cols
  8                 on col.owner = all_cols.owner
  9                and col.constraint_name = all_cols.constraint_name
 10         inner join all_constraints cons
 11                 on col.owner = cons.owner
 12                and col.constraint_name = cons.constraint_name
 13   where col.owner = user
 14     and col.table_name = 'FOO'
 15     and col.column_name = 'ID'
 16     and cons.constraint_type in ('U', 'P')
 17   order by owner, constraint_name, position;

C OWNER                          CONSTRAINT_NAME      TABLE_NAME      COLUMN_NAME            POSITION
- ------------------------------ -------------------- --------------- -------------------- ----------
U DTD_TRADE                      FOO_CON              FOO             ID                            1
U DTD_TRADE                      FOO_CON              FOO             ID2                           2
U DTD_TRADE                      FOO_CON2             FOO             ID                            1

回答by Aspirant

select count(*) cnt 
from user_constraints 
where table_name=your_table_name 
and constraint_type='U';

If count = 0 then there is not UNIQUEconstraint else there is UNIQUEconstraint on your table.

如果 count = 0 则没有UNIQUE约束，否则UNIQUE您的表有约束。

回答by Hans Zeller

Here is a query that I just tried. It lists each uniqueness constraint, identified by the index that enforces it, and the columns that are unique:

这是我刚刚尝试过的查询。它列出了每个唯一性约束，由强制执行它的索引标识，以及唯一的列：

select x.index_name, c.column_name, c.column_position
from USER_INDEXES x join USER_IND_COLUMNS c
     on x.index_name = c.index_name and x.table_name = c.table_name
     left join USER_CONSTRAINTS uc
     on x.index_name = uc.index_name and x.table_name = uc.table_name
where x.status = 'VALID' and
      (x.uniqueness = 'UNIQUE' or
       uc.constraint_type = 'U' and uc.status = 'ENABLED' and uc.validated = 'VALIDATED')
      and x.table_name='<your table name_in_caps>'
order by x.index_name, c.column_position;

It seems to work for primary keys, unique indexes, and added uniqueness constraints.

它似乎适用于主键、唯一索引和添加的唯一性约束。