ORA-00001: unique constraint (string.string) violated

ORA-00001: unique constraint (string.string) violated

Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.

Action: Either remove the unique restriction or do not insert the key.

---

the sample message might be as below

insert into student(pk_id, fieldname) values(1, '')
*
ERROR at line 1:
ORA-00001: unique constraint (PROMIS.SYS_C0012876) violated

With the example above we knew that it must be at table student. But if the query is more complex it might be hard to locate the problem constraint. If you already know where the prob is go to step 2 otherwise go to step 1

Step 1.> Locate the problem
To locate where might be the problem you may need to use the following query to find which table is your problem.
select distinct table_name
from all_indexes
where index_name = 'CONSTRAINT_NAME';

replace the CONSTRAINT_NAME with your constraint code (as the example above it is the text with the underline, SYS_C0012876. So the query would be:

select distinct table_name
from all_indexes
where index_name = 'SYS_C0012876';

and you will get the table name which cause the problem.

Step 2.> solve the problem
The options to resolve this Oracle ORA-00001 error are:

1. Modify your SQL so that a duplicate value is not created.
2. Change the constraint to allow duplicate values.
3. Diable the unique constraint.
4. Drop the unique constraint.

from 1-4 I would say it is likely that the first one is quite the right solution for me, as the constraint itself had the reason to be there from the first place. I suggest stick to number 1 to be on the save side, unless you know what you are doing.

Posted in: Ora Error