It won't drop the index if the index was separately created and non-unique.
When I said it would be consistent - I meant "if you do the same set of steps - it would either drop it or not - consistently"
So, I think you are doing something here - not sure what - and getting confused somewhere along the line. The index that is left behind is probably non-unique.
Unless you can show us an example...
In 9i, the drop constraint would drop the unique index. Going forward - if the index is created separately, it will not drop the index. You would use drop index on the drop constraint clause.
ops$tkyte%ORA9IR2> create table t ( x int, y int );
Table created.
ops$tkyte%ORA9IR2> create index t_idx on t(x);
Index created.
ops$tkyte%ORA9IR2> alter table t add constraint t_pk primary key(x);
Table altered.
ops$tkyte%ORA9IR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX NONUNIQUE
ops$tkyte%ORA9IR2> alter table t drop constraint t_pk ;
Table altered.
ops$tkyte%ORA9IR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX NONUNIQUE
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> drop table t;
Table dropped.
ops$tkyte%ORA9IR2> create table t ( x int, y int );
Table created.
ops$tkyte%ORA9IR2> create UNIQUE index t_idx on t(x);
Index created.
ops$tkyte%ORA9IR2> alter table t add constraint t_pk primary key(x);
Table altered.
ops$tkyte%ORA9IR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX UNIQUE
ops$tkyte%ORA9IR2> alter table t drop constraint t_pk ;
Table altered.
ops$tkyte%ORA9IR2> select index_name, uniqueness from user_indexes where table_name = 'T';
no rows selected
and in 10g and above:
ops$tkyte%ORA10GR2> create table t ( x int, y int );
Table created.
ops$tkyte%ORA10GR2> create index t_idx on t(x);
Index created.
ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(x);
Table altered.
ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX NONUNIQUE
ops$tkyte%ORA10GR2> alter table t drop constraint t_pk ;
Table altered.
ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX NONUNIQUE
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;
Table dropped.
ops$tkyte%ORA10GR2> create table t ( x int, y int );
Table created.
ops$tkyte%ORA10GR2> create UNIQUE index t_idx on t(x);
Index created.
ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(x);
Table altered.
ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX UNIQUE
ops$tkyte%ORA10GR2> alter table t drop constraint t_pk ;
Table altered.
ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX UNIQUE
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
<b>Note however that an implicitly created index will be dropped still</b>
ops$tkyte%ORA10GR2> drop table t;
Table dropped.
ops$tkyte%ORA10GR2> create table t ( x int, y int );
Table created.
ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(x);
Table altered.
ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_PK UNIQUE
ops$tkyte%ORA10GR2> alter table t drop constraint t_pk ;
Table altered.
ops$tkyte%ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
no rows selected