Cry about...
Oracle Troubleshooting

---

ORA-00001: unique constraint violated

---

Symptom:

When inserting or updating data, the following error is returned:

ORA-00001: unique constraint violated (<schema>.<constraint>)

Cause:

This error means that an attempt has been made to insert a record with a duplicate (unique) key. This error will also be generated if an existing record is updated to generate a duplicate (unique) key. Typically this is a duplicate primary key, but it need not be the primary key.

Remedy:

Only one of the following will be appropriate:

• Remove the unique restriction.
• Change the restriction to allow duplicate keys. An index could be changed to be a non-unique index, but remember that the primary key must always be unique.
• Do not insert the duplicate key.

Usually this error indicates an application error or error on the part of the user.

The error gives the name of the constraint that has been violated, but not the name of the table. To identify the table and find out all the relevant information about the index it is normally easiest to use Oracle Schema Manager - the name of the constraint given in the error will match with the name of the corresponding index. Alternately, to identify the name of the table use:

select table_name from all_indexes where index_name='<index-name>';

this view ('all_indexes') also contains some other information about the index, such as its uniqueness.

and to identify the files that together constitute the index:

select * from all_ind_columns where index_name='<index-name>';

---

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.