Oracle: Error ORA-00955 name is already used by an existing object

Sometimes things just won’t work as expected. You know, programming has its quirks and the like. Normally this error alone would mean you are creating some object with a name that is already in use. But what if you just deleted the previous object and get this error when you try to recreate it?

ERROR at line 1:
ORA-00955: name is already used by an existing object

ORA-00955 in SQL Developer

In Oracle, this if possible if you are deleting a unique constraint. This happens because a Unique Constraint is also an Index. Consider you just deleted the old constraint and now tries to create the updated constraint with the same name. Bang:  ORA-00955.

To fix it you must also delete the index that has the same name as the Unique Constraint. Then you will be able to create the constraint again, that will recreate the index too (with the same name – so much for the “name in use” rule, right?). BTW, the same happens with Primary Keys.

Actually, this is the kind of thing that makes life an adventure. Not always pleasant, but an adventure none the less. Below are some code snippets for your delight. Remember: Oracle is case-sensitive for text comparisons.

--Drop Unique Constraint
alter table mytable drop constraint UC_mytable;
--Drop Index for Unique Constraint
drop index myschema.UC_mytable;
--List all indexes from a table
select * from all_indexes where table_name = 'MYTABLE';
--List all constraints with a specific name
select * from all_constraints where constraint_name = 'UC_mytable';

And now some links for future reference also:
List of some ORA errors: http://docs.oracle.com/cd/A91202_01/901_doc/server.901/a90202/e900.htm

Some folks with the same problem: https://community.oracle.com/message/9907669 and http://stepintooracledba.blogspot.com.br/2013/03/ora-00955-name-is-already-used-by.html

That’s it for today. Cya!