主键列如何在 Oracle 中具有重复值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7612167/
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 can a primary key column have duplicated values in Oracle?
提问by Kemalettin Erbak?rc?
I have just tried to import an Oracle DB to another and it gave an error tells that duplicated values found in a primary key column. Then I checked the source table, and yes really there are duplicate values in PK, and checked PK is enabled and normal. Now I wonder how can this happen.
我刚刚尝试将一个 Oracle DB 导入另一个数据库，但它给出了一个错误，表明在主键列中找到了重复的值。然后我检查了源表，是的，PK中确实存在重复值，并且检查PK已启用且正常。现在我想知道这怎么会发生。
Edit: I found that index is in unusable state. I don't know how did it happen, just found this: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1859798300346695894
编辑：我发现索引处于不可用状态。我不知道它是怎么发生的，只是发现了这个：http: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::P11_QUESTION_ID: 1859798300346695894
回答by Vincent Malgrat
Assuming your primary key is indeed defined on this column, and enabled, you could check if it is validated. Only validated constraints are guaranteed by Oracle to be true for all rows.
假设您的主键确实在此列上定义并启用，您可以检查它是否经过验证。Oracle 仅保证经过验证的约束对所有行都为真。
Here's a scenario with an unvalidated primary key with a duplicate value:
这是一个带有重复值的未经验证的主键的场景：
SQL> DROP TABLE t;
Table dropped
SQL> CREATE TABLE t (ID NUMBER);
Table created
SQL> INSERT INTO t VALUES (1);
1 row inserted
SQL> INSERT INTO t VALUES (1);
1 row inserted
SQL> CREATE INDEX t_id_idx ON t(ID);
Index created
SQL> ALTER TABLE t ADD CONSTRAINT pk_id PRIMARY KEY (ID) NOVALIDATE;
Table altered
SQL> SELECT * FROM t;
ID
----------
1
1
SQL> SELECT constraint_type, status, validated
2 FROM user_constraints
3 WHERE constraint_name = 'PK_ID';
CONSTRAINT_TYPE STATUS VALIDATED
--------------- -------- -------------
P ENABLED NOT VALIDATED
Update:
更新：
One likely explanationis that a direct path load (from SQL*Loader) left your unique index in an unusable state with duplicate primary keys.
一种可能的解释是直接路径加载（来自 SQL*Loader）使您的唯一索引处于无法使用的状态，并且具有重复的主键。
回答by Kaushik Halvadia
A column that you think pk is applied but in reality its your mistake. PK is not defined on that column...
您认为 pk 已应用但实际上是您的错误的列。PK 未在该列上定义...
Or
或者
You have applied Composite/Multi column primary key.. Try to insert same record twice it shows error that means composite key..
您已应用复合/多列主键.. 尝试插入相同的记录两次它显示错误，这意味着复合键..
回答by Jeff Moore
Check to see that you have the index setup as unique.
检查您的索引设置是否唯一。
select table_name,uniqueness,index_name from all_indexes where table_name='[table_name]';
/* shows you the columns that make up the index */
select * from all_ind_columns where index_name='[index_name]';
回答by Florin Ghita
You had inserted data twice
你插入了两次数据
OR
或者
The destination table is not empty.
目标表不为空。
OR
或者
You defined a different pk on dest table.
您在 dest 表上定义了不同的 pk。
回答by Trevor North
Potentially the constraints were disabled to load data and then the same load has been run again- could be the index now won't validate because of the double data in the table.
可能约束被禁用以加载数据，然后再次运行相同的加载 - 可能是由于表中的双重数据，索引现在不会验证。
Has there been a data load recently ? Does the table have any audit columns eg create date to determine if all rowa are duplicate ?
最近有数据加载吗？该表是否有任何审计列，例如创建日期以确定所有 rowa 是否重复？
回答by m0rt1m3r
Using deferred constraints can cause duplicate values to be inserted into a PK column.
使用延迟约束会导致重复值插入到 PK 列中。
Check the link below. It describes a possible Oracle bug that can lead to duplicated primary key values. I just recreated the issue on Oracle 11g EE Rel 11.2.0.2.0
检查下面的链接。它描述了可能导致重复主键值的 Oracle 错误。我刚刚在 Oracle 11g EE Rel 11.2.0.2.0 上重新创建了这个问题
