How can a primary key column have duplicated values in Oracle?

Question

I have just tried to import an Oracle DB to another and it gave an error tells that duplicated values found in a primary key column. Then I checked the source table, and yes really there are duplicate values in PK, and checked PK is enabled and normal. Now I wonder how can this happen.

Edit: I found that index is in unusable state. I don't know how did it happen, just found this: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1859798300346695894

Answer 1

Assuming your primary key is indeed defined on this column, and enabled, you could check if it is validated. Only validated constraints are guaranteed by Oracle to be true for all rows.

Here's a scenario with an unvalidated primary key with a duplicate value:

SQL> DROP TABLE t;     
Table dropped

SQL> CREATE TABLE t (ID NUMBER);     
Table created

SQL> INSERT INTO t VALUES (1);     
1 row inserted

SQL> INSERT INTO t VALUES (1);     
1 row inserted

SQL> CREATE INDEX t_id_idx ON t(ID);     
Index created

SQL> ALTER TABLE t ADD CONSTRAINT pk_id PRIMARY KEY (ID) NOVALIDATE;     
Table altered

SQL> SELECT * FROM t;
 
        ID
----------
         1
         1
 
SQL> SELECT constraint_type, status, validated
  2    FROM user_constraints
  3   WHERE constraint_name = 'PK_ID';
 
CONSTRAINT_TYPE STATUS   VALIDATED
--------------- -------- -------------
P               ENABLED  NOT VALIDATED

---

Update:

One likely explanation is that a direct path load (from SQL*Loader) left your unique index in an unusable state with duplicate primary keys.