Today I was sitting in a public library, minding my own business when a man who I had never seen before, leaned over to me and asked me, "Is it true that you can drop and index created explicitly, but not one created implicitly as part of a constraint? Initially the reaction was implicit/explicit? Please speak English. When I sorted that out there were 2 further reactions, also unspoken, "Duh, that's pretty obvious", and "Why me?"
"It is an Ancient DBA and he stoppeth one of three."
I demonstrated the matter a bit to him.
Logged on as scott, I create a play table:
SQL> create table tmp(f1 number(4), f2 number(4), f3 number(4));
SQL> insert into tmp values (1, 2, 3);
1 row created.
SQL> create index tmp_f2 on tmp(f2);
SQL> drop index tmp_f2;
All straight forward. The index was created 'explicitly' and there is no constraint that it affects if it is dropped, so I can drop it without problem.
Now to add a constraint, and an 'implicit' index.
SQL> alter table tmp modify f2 unique;
SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TMP';
SQL> select index_name from user_indexes where table_name = 'TMP';
The constraint has been created as has an index
SQL> drop index SYS_C0011482;
drop index SYS_C0011482
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
and as expected, the index cannot be dropped since it is used by the constraint.
It is all very trivial.
What did get a little interesting was that I was able to enter multiple rows of nulls in a field with a 'unique' constraint. I need to review the properties of null.
And the real question that was never answered
"Now wherefore stopp'st thou me?"
A few basic notes on Oracle Database Administration.