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

While doing the Production deployment with the scripts given by application team, encountered the error.

This error which initially looked like strange but then came to know that Oracle works Like this..

##########################
#   Errors 
##########################

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

##########################
#   Command Used  
##########################


SQL> ALTER TABLE tracking ADD CONSTRAINT tracking_pk PRIMARY KEY (menu_id,asset_version);
ALTER TABLE tracking ADD CONSTRAINT tracking_pk PRIMARY KEY (menu_id,asset_version)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


##########################
#   Informations  

##########################

Initially i thought that a constraint exists with the same name TRACKING_PK 
But there is no constraint created with the same name in this schema. 

So started to Dig more on this...

##########################

#  Solution 
##########################

  Drop the indexes which were previously created as a part of Primary Key Constraint. 

The constraint which we are creating is a Primary Constriant. 

When we create a Primary Constraint, Oracle Creates 2 objects.

1) Constraint
2) Index

These 2 objects controls the uniqueness in the table.

So i checked DBA_INDEXES and found that a index with the same name TRACKING_PK still exists in the database which is not allowing to create a constraint with the same name TRACKING_PK. 

Select owner,index_name,index_type,table_owner,table_name from dba_indexes where table_name='tracking' and owner='AU_USER';

OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME 

------------------------------ ------------------------------------------------------------ 

AU_USER       tracking_PK                      AU_USER       tracking   

So Dropped the index and added the constraint and it worked.

Drop index "AU_USER"."tracking_PK";



SQL> ALTER TABLE tracking ADD CONSTRAINT tracking_pk PRIMARY KEY (menu_id,asset_version);

Table altered.