I have a strange situation that I don't understand. I am running a block of SQL in a merge module to update a oracle schema. I am trying to change the primary key of several tables so I am performing the following steps:
Drop FK constraints, Drop PK, Drop PK Index (if the index persists after the PK is dropped), Add new PK, Add FK's
Here's my problem. All is well until we get to the part where indexes are dropped. The primary is dropped and (to the best of my knowledge) any index that Oracle created based on that key will drop instantly as well. I was having issues with the index persisting so I added the DROP INDEX script to be certain it is removed, however, this is what happens:
Alter Table TABLE1 Drop Constraint TABLE1_PK
The command ran successfully
DROP INDEX TABLE1_PK
ORA-01418: specified index does not exist (This is exactly what I expected)
ALTER TABLE TABLE1 ADD (CONSTRAINT TABLE1_PK PRIMARY KEY (TABLE_KEY) ENABLE VALIDATE)
ORA-00955: name is already used by an existing object (The object being, in each case, the old index)
This of course prevents any of the FK's from linking because they are now based on the new key. When I run this in TOAD, the SQL works, but I can't figure out why it doesn't through the merge module. Can anyone help?
