While testing some migration scripts with a copy of production data (scripts run fine with development data) I found a curious situation. A CONSTRAINT has changed so I'm issuing DROP + ADD commands:
ALTER TABLE A_DUP_CALLE DROP CONSTRAINT A_DUP_CALLE_UK1; ALTER TABLE A_DUP_CALLE ADD CONSTRAINT A_DUP_CALLE_UK1 UNIQUE ( CONTROL_ID, CALLE_AYTO_DUPL ) ENABLE;
The DROP command worked fine but the ADD one failed. Now, I'm into a vicious circle. I cannot drop the constraint because it doesn't exist (initial drop worked as expected):
ORA-02443: Cannot drop constraint - nonexistent constraint
And I cannot create it because the name already exists:
ORA-00955: name is already used by an existing object
A_DUP_CALLE_UK1 into SQL Developer's Search box and... there it is! Owner, table name, tablescape... everything matches: it isn't a different object with the same name, it is my original constraint. The table appears in the constraint details but the constraint does not appear in the table's details.
- What's the explanation for this?
- How can I ensure it won't happen when I make the real upgrade in live server?
(Server is 10g XE, I don't have enough reputation to create the tag.)