ORA-00001 unique constraint violated

ORA-0001 unique constraint violated is one of the common message we often get while loading data.

ORA-00001 unique constraint violated

This error occurs when You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.

We can perform following action items for this error

  1.  You can look at the error and find the constraint information with the below sql
    SELECT column_name, position

    FROM all_cons_columns

    WHERE constraint_name = <<name of constraint from the error message>>

    AND owner           = <<owner of the table>>

    AND table_name      = <<name of the table>>

    Now we can check the existing data with the data we are inserting and then take action accordingly. You can change the keys so that they can be inserted

2)  We can drop the constraint if duplicate are allowed in the table

3) The 11gr2 hint ignore_row_on_dupkey_index allows the statement to silently ignore ORA-00001 errors.

Example

CREATE TABLE EXAMPLE_UNIQ(

EXAM_ID NUMBER NOT NULL ENABLE,

EXAM_NAME VARCHAR2(250) NOT NULL ENABLE

CONSTRAINT UK1_EXAMPLE UNIQUE (EXAM_ID, EXAM_NAME) ENABLE

);

table  created.

INSERT INTO EXAMPLE_UNIQ (EXAM_ID, EXAM_NAME) VALUES (1000, ‘XYZ’);

1 rows inserted.

Commit;

Commit completed

INSERT INTO EXAMPLE_UNIQ (EXAM_ID, EXAM_NAME) VALUES (1000, ‘XYZ’);

SQL Error: ORA-00001: unique constraint (UK1_EXAMPLE)  violated

Solution

INSERT INTO EXAMPLE_UNIQ (EXAM_ID, EXAM_NAME) VALUES (1001, ‘XYZ’);

1 rows inserted.

 

Related articles

ORA-00911: invalid character Common Issues and Resolution

ORA-03113: end-of-file on communication channel

How to resolve the ORA-00257 error in Oracle database

ORA-27154: post/wait create failed during startup

How to resolve ORA-29913 with external tables

ora-20001 in Gather schema stats on 11g(FND_HISTOGRAM_COLS)

Concurrent Manager:cleanup_node failed due to ORA-01427