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