CODEWITHSUNDEEP
oracle-databaseprimary-key
Kemalettin Erbakırcı
Kemalettin Erbakırcı

Reputation: 469

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

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

Upvotes: 8

Views: 28887

Answers (6)

m0rt1m3r
m0rt1m3r

Reputation: 180

Using deferred constraints can cause duplicate values to be inserted into a PK column.

Check the link below. It describes a possible Oracle bug that can lead to duplicated primary key values. I just recreated the issue on Oracle 11g EE Rel 11.2.0.2.0

http://www.pythian.com/news/9881/deferrable-constraints-in-oracle-11gr2-may-lead-to-logically-corrupted-data/

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67762

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.

Upvotes: 8

Trevor North
Trevor North

Reputation: 2296

Potentially the constraints were disabled to load data and then the same load has been run again- could be the index now won't validate because of the double data in the table.

Has there been a data load recently ? Does the table have any audit columns eg create date to determine if all rowa are duplicate ?

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

You had inserted data twice

OR

The destination table is not empty.

OR

You defined a different pk on dest table.

Upvotes: 0

Jeff Moore
Jeff Moore

Reputation: 444

Check to see that you have the index setup as unique.

select table_name,uniqueness,index_name from all_indexes where table_name='[table_name]';


/* shows you the columns that make up the index */
select * from all_ind_columns where index_name='[index_name]';

Upvotes: 0

Kaushikkumar Halvadia
Kaushikkumar Halvadia

Reputation: 869

A column that you think pk is applied but in reality its your mistake. PK is not defined on that column...

Or

You have applied Composite/Multi column primary key.. Try to insert same record twice it shows error that means composite key..

Upvotes: 0

Related Questions