ORA-00955 "name is already used by an existing object"
oracle, oracle11g
Solution
Perhaps there is an `INDEX` associated with the `PRIMARY KEY CONSTRAINT`, and it is also named as `PK_B`.
You can check it as :
SELECT * FROM USER_INDEXES WHERE TABLE_NAME='<table_name>';
If that's true, then do :
ALTER INDEX "PK_B" RENAME TO "PK_XYZ";
Update : Regarding `ALTER INDEX` statement, few important points as mentioned by Justin in the comments
Oracle implicitly creates an `UNIQUE` index to support the `PRIMARY KEY CONSTRAINT`. Since, the index is of the same name that of the primary key, and now that the primary key is being modified, it is better to drop and re-create the index again as per the definition of the old primary key.
My conclusion :
- The primary key constraint is enforced through a unique index.
- If Oracle already finds an index – unique or non-unique – it uses it for the primary key.
- If the index was initially created as non-unique, it will continue to show as non-unique, however it will actually be a unique index.
A good demonstration and quite detailed on other aspects too, by Arup : Primary Keys Guarantee Uniqueness? Think Again.
Problem
I need to modify an existing PK. Therefore I drop an recreate it. ``` ALTER TABLE B DROP CONSTRAINT PK_B; ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY ("TYP", "NR", "HH", "QUART"); ``` Unfortunately the last Statement will give me an error ORA-00955 If I create the PK constraint like it was defined originally with: ``` ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY ("TYP", "NR", "HH"); ``` everything works fine.