ORA-00001: Unique constraint 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.

Error:	ORA-00001: unique constraint (constraint_name) violated
Cause:	You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.
Action:	The options to resolve this Oracle error are: Drop the unique constraint Change the constraint to allow duplicate values Modify your SQL so that a duplicate value is not created If you are not sure which unique constraint was violated, you can run the following SQL: select distinct table_name from all_indexes where index_name = 'CONSTRAINT_NAME';

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

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 11gr2 hint ignore_row_on_dupkey_index allows the statement to silently ignore ORA-00001 errors. 

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

1) Remove the unique restriction.
2) 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
3) Do not insert the duplicate key

Typically an application error message.

The constraint indicated in the error message is a unique or primary key on a table.

An attempt was made to insert a record with a key that already exists in that table. This is not allowed and thus violates the constraint.

you can use following statement to identify the columns of the table this constraint is based upon:

select table_name, column_name
from all_cons_columns
where owner='<Here OWNER>'
and constraint_name='<Here CONSTRAINT_NAME>'
order by position;

Problem

On a Oracle Environment, when changing the group in console or amending a users Group in administration you see the follow error upon save:

ORA-00001: unique constraint (OOTB_EN.INDEX$TPS_LIC_AUDIT_ITEM$T) violated

Answer

Please have the below script run by your Oracle DB administrator against your DB, ensuring a full backup has been done and everyone is logged out of the system.  Where possible test on a test system before applying to live.

DROP INDEX index$tps_lic_audit_item$t ON tps_licence_audit_item;

CREATE INDEX index$tps_lic_audit_item$t ON tps_licence_audit_item(tps_title);

Note: If the above script errors with the message "ORA-00955: name is already used by an existing object", then use this script:

DROP INDEX index$tps_lic_audit_item$t;

CREATE INDEX index$tps_lic_audit_item$t ON tps_licence_audit_item(tps_title);