SQL ORA-02437: 无法验证 <name> - 违反了主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1453892/
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
ORA-02437: cannot validate <name> - primary key violated
提问by blackicecube
I have a table:
我有一张桌子：
CREATE TABLE MY_TABLE (
MY_ID NUMBER NOT NULL,
COLUMN_1 NUMBER,
COLUMN_2 NUMBER
);
ALTER TABLE MY_TABLE ADD CONSTRAINT PK_FOO PRIMARY KEY (MY_ID);
at a later point, when executing the following sql, I get an error:
稍后，在执行以下 sql 时，出现错误：
ALTER TABLE MY_TABLE DROP PRIMARY KEY DROP INDEX;
ALTER TABLE MY_TABLE ADD CONSTRAINT PK_FOO PRIMARY KEY (MY_ID)
ORA-02437: cannot validate PK_FOO - primary key violated
My table only contains 3 entries all with a different primary key which is also not null. Anyone has an idea what this could be?
我的表只包含 3 个条目，所有条目都具有不同的主键，主键也不为空。任何人都知道这可能是什么？
Thanks,
谢谢，
Peter
彼得
回答by APC
My table only contains 3 entries all with a different primary key which is also not null.
我的表只包含 3 个条目，所有条目都具有不同的主键，主键也不为空。
You must forgive a certain amount of scepticism on our part. Because that error definitely indicates a duplicate value.
你必须原谅我们的一定程度的怀疑。因为该错误肯定表明存在重复值。
What you need to do is use the exceptionsclause. This will show you the ROWIDs of the records which are violating your constraint. You may need to create the target table: by default the script creates a table called EXCEPTIONS:
您需要做的是使用该exceptions条款。这将显示违反约束的记录的 ROWID。您可能需要创建目标表：默认情况下，脚本会创建一个名为 EXCEPTIONS 的表：
SQL> ALTER TABLE MY_TABLE ADD CONSTRAINT PK_FOO PRIMARY KEY (MY_ID);
ALTER TABLE MY_TABLE ADD CONSTRAINT PK_FOO PRIMARY KEY (MY_ID)
*
ERROR at line 1:
ORA-02437: cannot validate (APC.PK_FOO) - primary key violated
SQL> @%ORACLE_HOME%\rdbms\admin\utlexpt1.sql
Table created.
SQL> ALTER TABLE MY_TABLE ADD CONSTRAINT PK_FOO PRIMARY KEY (MY_ID)
2 exceptions into exceptions
3 /
ALTER TABLE MY_TABLE ADD CONSTRAINT PK_FOO PRIMARY KEY (MY_ID)
*
ERROR at line 1:
ORA-02437: cannot validate (APC.PK_FOO) - primary key violated
SQL> select * from exceptions
2 /
ROW_ID OWNER TABLE_NAME CONSTRAINT
------ ----- ---------- ----------
AABQXcAAEAAAXUPAAD APC MY_TABLE PK_FOO
AABQXcAAEAAAXUPAAB APC MY_TABLE PK_FOO
SQL>
Edit
编辑
You need to figure out what is different between your install code and the simplification you posted here. The chances are you have one or more INSERTstatements which are accidentally executed more than once while the constraint is not in force. Adding the EXCEPTIONS INTOclause to your code might help you track it down.
您需要弄清楚您的安装代码和您在此处发布的简化之间的区别。很可能您有一个或多个INSERT语句在约束未生效时意外执行了不止一次。将EXCEPTIONS INTO子句添加到您的代码中可能会帮助您追踪它。
回答by Aziz
回答by Heiko Hatzfeld
Are 2 primary keys identical?
2个主键是否相同？
This error is usually thrown when you try to create/enable a primary key on a table
当您尝试在表上创建/启用主键时，通常会抛出此错误
回答by dpbradley
I'm upvoting APC's answer, but since you seem to have some problems implementing it, could you just post the results of this query:
我赞成 APC 的回答，但是由于您在实施它时似乎遇到了一些问题，您能否发布此查询的结果：
select my_id, count(*) from my_table group by my_id having count(*) >1
This will give us (and you) some idea of the problematic keys.
这将使我们（和您）对有问题的密钥有所了解。
