The Oracle (tm) Users' Co-Operative FAQ

I am unable to add a PK constraint to a table - what is the best way of finding the problem rows ?

Back to index of questions

We've all been there - you issue an "alter table ... add primary key" command, and sit and watch for 12 hours whilst it chugs through your "squillion" row table only to come back with "ORA-02437: cannot validate ..." or similar because you have some problem rows. And since it failed - you are no closer to resolving the problem.

The solution is to break the task down into stages:

• Create an NON-UNIQUE index on the appropriate table column(s)
• Add the primary with the NOVALIDATE clause. This stops any more problem rows getting into the table
• Alter the primary key with the VALIDATE EXCEPTIONS INTO clause. You will still see the standard 'cannot validate' error, but now the EXCEPTIONS table (cf: $ORACLE_HOME/rdbms/admin/utlexcpt.sql) contains the rowids for the problem rows
• Remove the problem rows
• Alter the primary key with the VALIDATE clause

Further reading: N/A

Back to top

Back to index of questions