ORA-00001 unique constraint violated



Last updated on April 1st, 2017 at 08:29 am

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

 

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.

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

ORA-00001 unique constraint violated

The IGNORE_ROW_ON_DUPKEY_INDEX hint are unlike other hints in that they have a semantic effect. The general philosophy explained in “Hints” does not apply for these three hints.

The IGNORE_ROW_ON_DUPKEY_INDEX hint applies only to single-table INSERT operations. It is not supported for UPDATE, DELETE, MERGE, or multitable insert operations. IGNORE_ROW_ON_DUPKEY_INDEX causes the statement to ignore a unique key violation for a specified set of columns or for a specified index. When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.

The semantic effect of this hint results in error messages if specific rules are violated:

If you specify index, then the index must exist and be unique. Otherwise, the statement causes ORA-38913.

You must specify exactly one index. If you specify no index, then the statement causes ORA-38912. If you specify more than one index, then the statement causes ORA-38915.

You can specify either a CHANGE_DUPKEY_ERROR_INDEX or IGNORE_ROW_ON_DUPKEY_INDEX hint in an INSERT statement, but not both. If you specify both, then the statement causes ORA-38915.

As with all hints, a syntax error in the hint causes it to be silently ignored. The result will be that ORA-00001 will be caused, just as if no hint were used.

insert /*+ ignore_row_on_dupkey_index(unique_table, unique_table_idx) */

into

unique_table

(select * from non_unique_table);

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


Leave a Reply