ORA-02437: cannot validate <name> - primary key violated

Question

Hi, 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:

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?

Thanks,

Peter

Answer 1

Are 2 primary keys identical?

This error is usually thrown when you try to create/enable a primary key on a table

Answer 2

from here

Cause: You tried to tried to enable a primary key constraint, but the columns in the primary key either contained NULL values or duplicates..

Answer 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 exceptions clause. 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:

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 INSERT statements which are accidentally executed more than once while the constraint is not in force. Adding the EXCEPTIONS INTO clause to your code might help you track it down.

Answer 4

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:

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.