A trivial index demo

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));

Table created.

SQL> insert into tmp values (1, 2, 3);

1 row created.

SQL> create index tmp_f2 on tmp(f2);

Index created.

SQL> drop index tmp_f2;

Index dropped.


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;

Table altered.

SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TMP';

CONSTRAINT_NAME
------------------------------
SYS_C0011482

SQL> select index_name from user_indexes where table_name = 'TMP';

INDEX_NAME
------------------------------
SYS_C0011482

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?"