如何确定哪个 UNIQUE 索引在 Oracle 中强制执行 PRIMARY KEY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15291855/
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
How to determine which UNIQUE index enforces the PRIMARY KEY in Oracle
提问by ddevienne
Given a table with two unique indexes, as shown below, how does one determine which unique index corresponds to the PRIMARY KEY, as opposed to other UNIQUE indexes on that table?
给定一个具有两个唯一索引的表，如下所示，如何确定哪个唯一索引对应于 PRIMARY KEY，而不是该表上的其他 UNIQUE 索引？
The user_indexes and user_ind_columns do not seem to carry that information. Thanks, --DD
user_indexes 和 user_ind_columns 似乎没有携带该信息。谢谢，--DD
PS: Short of explicitly naming the indexes and using a naming convention of course.
PS：当然没有明确命名索引并使用命名约定。
SQL> create table tt (id number not null primary key, name varchar2(64 char) not null unique, info varchar2(4000));
SQL> create index tt_idx on tt(info);
SQL> select INDEX_NAME, INDEX_TYPE, UNIQUENESS from user_indexes where TABLE_NAME='TT';
INDEX_NAME INDEX_TYPE UNIQUENES
-------------------- --------------------------- ---------
SYS_C0029541 NORMAL UNIQUE
SYS_C0029542 NORMAL UNIQUE
TT_IDX NORMAL NONUNIQUE
SQL> select * from user_ind_columns where TABLE_NAME='TT';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
-------------------- -------------------- ------------ --------------- ------------- ----------- ----
SYS_C0029541 TT ID 1 22 0 ASC
SYS_C0029542 TT NAME 1 256 64 ASC
TT_IDX TT INFO 1 4000 4000 ASC
Update:
更新：
Here's the result of running Florin's query, which clearly shows which index enforces the PRIMARY KEY:
下面是运行弗罗林的查询，这清楚地表明该指数强制执行的结果PRIMARY KEY：
SQL> select constraint_name, constraint_type, index_name from user_constraints where table_name='TT';
CONSTRAINT_NAME C INDEX_NAME
------------------------------ - --------------------
SYS_C0029539 C
SYS_C0029540 C
SYS_C0029541 P SYS_C0029541
SYS_C0029542 U SYS_C0029542
回答by David Aldridge
Primary keys can be supported by a non-unique index, so it's not really the index that enforces uniqueness at all, it's the constraint itself.
非唯一索引可以支持主键，因此它根本不是强制唯一性的索引，而是约束本身。
If you create a primary key or unique key against a set of columns then by default a unique index is created, except that if the constraint is declared to be deferrable then a non-unique index is created. A primary key or unique key can also be declared against a set of columns that are covered by a non-unique index without a new unique index being created.
如果您针对一组列创建主键或唯一键，则默认情况下会创建唯一索引，除非将约束声明为可延迟，然后创建非唯一索引。还可以针对由非唯一索引覆盖的一组列声明主键或唯一键，而无需创建新的唯一索引。
So, it is not the uniqueness of a supporting index that enforces a primary key constraint, any more than it is the presence of a NOT NULL column constraint that enforces that a primary key column must be non-null -- in both cases it is the primary key constraint itself that is responsible.
因此，强制主键约束不是支持索引的唯一性，就像强制主键列必须为非空的 NOT NULL 列约束的存在一样——在这两种情况下都是负责的主键约束本身。
You should definitely name all of your constraints though, apart from column NOT NULL declarations.
除了列 NOT NULL 声明之外，您绝对应该命名所有约束。
回答by Florin Ghita
select
owner, constraint_name, table_name, index_name
from
user_constraints
where constraint_type in ('P');
回答by ibre5041
In rare cases indexes columns can be a superset of PK's columns. For example index on (A, B, C) while PK?is only?(A, B).
在极少数情况下，索引列可以是 PK 列的超集。例如索引 (A, B, C) 而 PK? is only?(A, B)。
回答by Vincent Malgrat
As your query shows, you have only one index on the primary key column, there is no confusion possible here !
正如您的查询所示，您在主键列上只有一个索引，这里不可能出现混淆！
The unique index on column ID(SYS_C0029541) is used to enforce the primary key.
列ID( SYS_C0029541)上的唯一索引用于强制执行主键。
In the general case you can use the INDEX_NAMEcolumn of the USER_CONSTRAINTSview to find the index used to police a unique constraint or a primary key:
在一般情况下，您可以使用视图的INDEX_NAME列USER_CONSTRAINTS来查找用于监管唯一约束或主键的索引：
SELECT constraint_name, index_name, constraint_type
FROM user_constraints
WHERE table_name = 'TT';
