ORA-02429: cannot drop index used for enforcement of unique/primary key


====================================
---when I execute
SQL> drop index IND_ON_ANAR_TEST ; ----its reply
ORA-02429: cannot drop index used for enforcement of unique/primary key
If it is an unique index that enforces unique constraint 
you cannot simply drop it
You have to drop unique constraint BY
alter table tablename drop constraint constraintname;
=======================================
Hands on Example
======================================
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jun 30 16:21:01 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> CONN STLBAS/STLBAS@JUNCLS
Connected.
SQL>
SQL>
SQL> DROP TABLE ANAR_TEST ;
Table dropped.
SQL>
SQL> CREATE TABLE ANAR_test (id number,name varchar2(30)) ;
Table created.
SQL>
SQL>
SQL> INSERT INTO ANAR_test VALUES (100,'Mr. ANAR');
1 row created.
SQL>
SQL>
SQL> INSERT INTO ANAR_test VALUES (100,'Mr Cadot');
1 row created.
SQL>
SQL>
SQL> INSERT INTO ANAR_test VALUES (200,'Mr. Arif');
1 row created.
SQL>
SQL>
SQL> INSERT INTO ANAR_test VALUES (300,'Mr. Akash');
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL>
SQL> create index IND_ON_ANAR_TEST on ANAR_test(ID);
Index created.
SQL> ALTER TABLE ANAR_test ADD PRIMARY KEY (ID) NOVALIDATE ;
Table altered.
SQL>
SQL>
SQL>
SQL> DROP index IND_ON_ANAR_TEST ;
DROP index IND_ON_ANAR_TEST
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL>
SQL>
SQL>
SQL>
SQL> SELECT owner, constraint_name, constraint_type, table_name
2 FROM user_constraints
3 WHERE table_name = 'ANAR_TEST';
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ----------------
STLBAS SYS_C0084077 P ANAR_TEST
SQL>
SQL>
SQL> alter table ANAR_test drop constraint SYS_C0084077 ;
Table altered.
SQL>
SQL>
SQL>
SQL>
SQL> SELECT index_name, index_type, table_owner, table_name, table_type,
2 uniqueness
3 FROM user_indexes
4 WHERE table_name = 'ANAR_TEST';
INDEX_NAME INDEX_TYPE TABLE_OWNER
TABLE_NAME TABLE_TYPE UNIQUENES
------------------------------ --------------------------- ---------------------
--------- ------------------------------ ----------- ---------
IND_ON_ANAR_TEST NORMAL STLBAS
ANAR_TEST TABLE NONUNIQUE
SQL>
SQL>
SQL> drop index IND_ON_ANAR_TEST;
Index dropped.

		

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: