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
##########################
# 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 But there is no constraint created with the same name in this schema.
So started to Dig more on this...
##########################
##########################
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.
SQL> ALTER TABLE tracking ADD CONSTRAINT tracking_pk PRIMARY KEY (menu_id,asset_version);
Table altered.
