ORA-00955 "already in use by an existing object"

Question

I need to modify an existing PC. So I throw it to recreate.

ALTER TABLE B DROP CONSTRAINT PK_B; ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY ("TYP", "NR", "HH", "QUART"); 

Unfortunately, the last expression will give me error ORA-00955

If I create a PK constraint as defined initially with:

 ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY ("TYP", "NR", "HH"); 

everything is working fine.

Answer 1

There may be an INDEX associated with the PRIMARY KEY CONSTRAINT , and it is also called PK_B .

You can check it as:

 SELECT * FROM USER_INDEXES WHERE TABLE_NAME='<table_name>'; 

If this is true then do:

 ALTER INDEX "PK_B" RENAME TO "PK_XYZ"; 

Update: Regarding the ALTER INDEX statement, a few important points mentioned by Justin in the comments

Oracle implicitly creates a UNIQUE index to support PRIMARY KEY CONSTRAINT . Since the index has the same name as the primary key, and now that the primary key is changing, it is best to reset and re-create the index according to the definition of the old primary key.

My conclusion:

• Primary key restriction is performed using a unique index.
• If Oracle already finds the index - unique or not unique - it uses it for the primary key.
• If the index was originally created as not unique, it will continue to show as not unique, however it will be a truly unique index.

Good demo and fairly detailed information on other aspects as well, Arup: Uniqueness of unique keys? Think again.

Answer 2

I had the same problem when I had to do the following in order to remove a table link from a view when recreating the database from scratch. At first I looked for the same thing in tables and indexes.

 connect sys/oracle as sysdba; select * from all_tables select * from all_indexes (finally located the reference in the views) select * from all_views where view_name like '%WKSTSTATE%'; drop view RUEGEN.WKSTSTATE;