3

When I drop a unique column (just unique not primary key) constraint, Toad prompts me whether if I want to keep the index.

To me the logical thing is to remove the index but why does it offer to keep it ? Is it for saving time if I make the same column unique at a later point in time ? (which kind of confuses me since the old index may cause conflicts).

Cemre Mengü
  • 18,062
  • 27
  • 111
  • 169
  • 1
    read some considerations at [http://stackoverflow.com/questions/7521817/oracle-unique-constraint-and-unique-index-question](http://stackoverflow.com/questions/7521817/oracle-unique-constraint-and-unique-index-question) – Florin Ghita Apr 01 '13 at 13:15

1 Answers1

3

As of 10g, Oracle introduced the "KEEP INDEX" option on ALTER TABLE...DROP CONSTRAINT. From what I've read, it should be the other way around...in that it should drop the index unless you specify "KEEP INDEX". So I am assuming that you removed the index via the GUI menus.

With that in mind, the TOAD GUI was probably designed to prompt you for that. I wonder if you actually ran ALTER TABLE...DROP CONSTRAINT if it would still prompt you. I am going to bet "not."

Aaron
  • 55,518
  • 11
  • 116
  • 132
  • Yes running queries directly from editor does not cause any prompts (for everything actually) – Cemre Mengü Apr 01 '13 at 13:24
  • I still wonder though: why would someone want to keep the index after dropping the constraint ? – Cemre Mengü Apr 01 '13 at 20:17
  • I suppose it would be a rare situation, but I think it's more intended for removing a foreign key constraint. That way you could remove the constraint if you didn't want to enforce referential integrity between two tables. But you might still have it in some JOIN/WHERE clauses buried in application code. – Aaron Apr 01 '13 at 20:46