If it is an unique index that enforces unique constraint you cannot simply
drop it
You have to drop unique constraint
alter table tablename drop constraint constraintname;
an example follows
SQL> desc qaqa1
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER(5)
COL2 VARCHAR2(4000)
SQL> select * from user_constraints where table_name = 'QAQA1';
no rows selected
SQL> select * from user_indexes where table_name = 'QAQA1';
no rows selected
SQL> alter table qaqa1 add constraint zzz unique (col1);
Table altered.
SQL> select * from user_constraints where table_name = 'QAQA1';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ -
-----------------------
MANTAOWNER ZZZ U QAQA1
SQL> select * from user_indexes where table_name = 'QAQA1';
INDEX_NAME INDEX_TYPE TABLE_OWNER
------------------------------ ---------------------------
----------------------------
ZZZ NORMAL MANTAOWNER
SQL> drop index zzz;
drop index zzz
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL> alter table qaqa1 drop constraint zzz;
Table altered.
SQL> select * from user_constraints where table_name = 'QAQA1';
no rows selected
SQL> select * from user_indexes where table_name = 'QAQA1';
no rows selected
Gints Plivna
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
