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

Question

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

My table only contains 3 entries all with a different primary key which is also not null.

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>

What do you do after you look at your exception table?

The exception table shows us all the rows which are preventing us from adding the constraint. In the example we have two rows which have a duplicate value in the primary key column. So we need to examine those rows to see what the problem is:

select * from my_table
where rowid in (select row_id from exceptions)

What we do after that depends on what we find.

If the two rows are exact duplicates we can simply delete one row and add the constraint.

If the rows are different (same key value, divergent non-key values) then we must discover whether:

• they're two versions of the same row, in which case we need to determine the most recent version and zap the other
• they're supposed to be different records, in which case we need to change the key value for one of the rows to something unique.

In all cases we should find out why our table contains duplicate keys so we can prevent it happening again. Obviously enforcing the primary key will stop the duplicates but it's better to fix teh underlying problem as well, rather than waiting for something to fail in Production.

I'll tried it... Unfortunately I could not reproduce the problem when simply executing these few sql snippets

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.