How do primary key columns have duplicate values ​​in Oracle?

Question

I just tried to import Oracle DB into another and it gave an error saying that duplicate values ​​were found in the primary key column. Then I checked the source table and yes, indeed, PK has duplicate values, and the verified PK is also normal. Now I wonder how this can happen.

Edit: I found that the index is in an unusable state. I don’t know how it happened, just found it: http://asktom.oracle.com/pls/asktom/f?p=100:11:59::::P11_QUESTION_ID:1859798300346695894

Answer 1

Assuming your primary key is indeed defined in this column and is included, you can check if it is verified. Only verified constraints are guaranteed by Oracle as true for all rows.

Here's a script with an incomplete 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 loading the direct path (from SQL * Loader) left your unique index in an unusable state with duplicate primary keys.

Answer 2

The column that you think pk applies to, but is actually a mistake. PK is not defined in this column ...

or

You have applied the Composite / Multi column primary key. Try inserting the same record twice, showing an error that means a composite key.

Answer 3

Make sure you have the index setting installed.

 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]'; 

Answer 4

You inserted data twice

OR

The destination table is not empty.

OR

You have defined another pk table for dest.

Answer 5

Potentially, restrictions were disabled for loading data, and then the same load was loaded again - maybe now the index will not be checked due to duplicate data in the table.

Was the information recently uploaded? Are there any audit columns in the table, for example, creating a date to determine if all rows in a row are repeated?

Answer 6

Using pending constraints may cause duplicate values ​​to be inserted into the PK column.

Check out the link below. It describes a possible Oracle error that could lead to duplicate primary key values. I just recreated the Oracle 11g EE Rel 11.2.0.2.0 question

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