When I rename a unique key CONSTRAINT (eg from WFNAME to WFN_NAME)
TOAD compare tries to drop via:
Prompt drop Index WFNAME;
DROP INDEX UTILS_DAO.WFNAME;
this results in:
<<
DROP INDEX UTILS_DAO.WFNAME
*ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
The constraint should be dropped first via:
ALTER TABLE UTILS_DAO.GEN_WORKFLOW_DEFINITIONS
DROP CONSTRAINT WFNAME;
(even better would be renaming the constraint + index if the columns remain the same, but this is not a big issue for me, would only be an issue on very large indexes)
Any advice on this?
Regards
Christophe
Hmmm... what version of Toad are you running? If you're running an older version, it's very possible that Toad is doing something more optimal. I just ran a quick test on my 15.0 version, and Toad is doing a RENAME after an index re-build (because the columns were NOT the same). See snap below...
Is the same thing happening when you upgrade to 15.0?
Hmmm... I just tested my same compare with Toad v. 14.-- same version and build as yours-- and I get my previous results.. e.g. a sync script that RENAMEs the constraint after an index rebuild.
I tried this on 14.2 and I only get rename statements (First the constraint, then the index)
To set up, I made a schema SCOTT2 that was identical to SCOTT, then I renamed the PK_DEPT constraint to PKDEPT. Toad's schema compare just gave me RENAME CONSTRAINT command for that. Then I also renamed the index and got the below. Maybe some other differences also need to exist for the drop statement to happen. If you have any more info, please let me know and I'll look at it.
