Ora-02437 When Creating PK Using Novalidate When Table Has duplicate data iPK Columns

Subject: Ora-02437 When Creating PK Using Novalidate When Table Has duplicate data iPK Columns
Doc ID: Note:264003.1 Type: PROBLEM
Last Revision Date: 02-JUL-2007 Status: MODERATED


The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.0
This problem can occur on any platform.

Errors
ORA 2437 cannot enable - primary key violated


Symptoms
Have a table with duplicate data in the PK columns.
Trying to create the PK with the novalidate option fails with an ora-02437.

Test Case to exhibit the problem:

CREATE TABLE chris (col1 INTEGER)
/
INSERT INTO chris VALUES (1)
/
INSERT INTO chris VALUES (2)
/
INSERT INTO chris VALUES (3)
/
INSERT INTO chris VALUES (4)
/
INSERT INTO chris VALUES (4)
/
ALTER TABLE chris ADD PRIMARY KEY (col1) NOVALIDATE
/
-- fails with error ORA-02437
ALTER TABLE chris ADD PRIMARY KEY (col1) DISABLE
/
-- ok
ALTER TABLE chris ENABLE NOVALIDATE PRIMARY KEY
/
-- again fails with error ORA-02437

Cause
This error occurs because it tries to create a unique index for the unique or primary
key index. The uniqueness is checked during the creation of the unique index.
Fix
- First, create a NON-UNIQUE index on the same fields that you want to
include in the constraint.
- Then add the unique or primary key constraint.

Thats is:

create index idx_chris_col1 on chris(col1);

After this give:

ALTER TABLE chris ADD PRIMARY KEY (col1) NOVALIDATE
/

OR

ALTER TABLE chris ENABLE NOVALIDATE PRIMARY KEY
/

References
Bug 544502 - Enabling Novalidate Not Deferrable Unique Constraint Creates Unique Index

index rebuild

The index is valid during rebuild or rebuild online if it is valid before rebuild.
Query started during rebuild or rebuild online can use this index.

How to find which indexes are used and which indexes are NOT used?

In Oracle10g we can easily see what indexes are used, when they are used and the context where they are used.

select
p.object_name c1,
p.operation c2,
p.options c3,
count(1) c4
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%' and
p.sql_id = s.sql_id
group by
p.object_name,
p.operation,
p.options
order by
1,2,3;